SQL Foreign Key Constraint
SQL Foreign Key constraint establishes a relationship between two tables by requiring values in one table’s column to match values in another table’s primary key column.
Foreign Key in SQL
A foreign key is a column or a combination of columns in a table that establishes a link between two tables in a relational database. It refers to the primary key in another table, creating a relationship between them.
The foreign key constraint enforces referential integrity, ensuring that values in the foreign key column must match values in the primary key column of the referenced table.
A foreign key is created in the CREATE TABLE or ALTER TABLE statement. The foreign key in a table should match the primary key in the referenced table for every row. This is called Referential Integrity. Foreign key ensures referential integrity.
The table in which a foreign key is defined is called a Foreign table/Child table/Referencing table. and the table that defines a primary key and is referenced by a foreign key is called a Primary table/Parent table /Referenced Table
Properties:
- The parent field that is being referenced has to be unique/Primary Key.
- The child field may have duplicates and nulls.
- Parent records can be deleted if no child exists.
- The master table cannot be updated if a child exists.
- Must reference PRIMARY KEY in the primary table.
- The foreign key column and constraint column should have matching data types.
- Records cannot be inserted in the child table if a corresponding record in the master table does not exist.
- Records of the master table cannot be deleted if corresponding records in the child table exist.
Syntax
There are two ways to add a foreign key to the table in SQL – CREATE TABLE and ALTER TABLE statement
SQL FOREIGN KEY on CREATE TABLE
The syntax to create a foreign key on CREATE TABLE statement is:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
…,
CONSTRAINT fk_constraint_name FOREIGN KEY (column1, column2, …)
REFERENCES parent_table(column1, column2, …));
SQL FOREIGN KEY on ALTER TABLE
The syntax to create a foreign key on ALTER TABLE statement is:
ALTER TABLE table_name
ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column1, column2, …)
REFERENCES parent_table(column1, column2, …);
SQL Foreign Key Constraint Example
Let’s look at some examples of the FOREIGN KEY Constraint in SQL.
First, we will create two tables ‘customers’ and ‘order’.
Query
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50) NOT NULL
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderNumber INT NOT NULL,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');
INSERT INTO Orders (OrderID, OrderNumber, CustomerID)
VALUES (1, 101, 1), (2, 102, 2), (3, 103, 3);
The following query will create the ‘customers’ and ‘orders’ table.
Customers Table
| CustomerID (Primary Key) | CustomerName |
|---|---|
| 1 | John |
| 2 | Jane |
| 3 | Bob |
| OrderID (Primary Key) | OrderNumber | CustomerID (Foreign Key) |
|---|---|---|
| 1 | 101 | 1 |
| 2 | 102 | 2 |
| 3 | 103 | 3 |
Insert Operation in Foreign Key Table Example
If a corresponding value in the foreign table doesn’t exist, a record in the child table cannot be inserted.
Query:
INSERT INTO Orders (OrderID, OrderNumber, CustomerID)
VALUES (4, 104, 4);
Output :
Error: FOREIGN KEY constraint failed.
Delete Operation in Foreign Key Table
When a record in the master table is deleted and the corresponding record in the child table exists, an error message is displayed and prevents the DELETE operation from going through.
Query:
DELETE FROM Customers
WHERE CustomerID = "3";
Output:
Error: FOREIGN KEY constraint failed
SQL DROP FOREIGN KEY
To remove a Foreign key from a table, use the ALTER TABLE command. The syntax to drop a primary key is:
Syntax
ALTER TABLE table_name DROP CONSTRAINT fk_name;
Important Points About SQL FOREIGN KEY Constraint
- A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
- The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
- A table can have multiple FOREIGN KEY constraints.
- When defining a FOREIGN KEY constraint, you can specify what happens when a referenced row in the parent table is deleted or updated. This is done using the ON DELETE and ON UPDATE clauses followed by the CASCADE, SET NULL, or NO ACTION option.
- The FOREIGN KEY constraint in SQL is used to maintain the referential integrity of data within the database.