SQL FOREIGN KEY Constraint
A FOREIGN KEY is a key used to link two tables together.
The difference between Primary key, Unique key and Foreign Key is the most common interview question for.NET as well as SQL developers. The PRIMARY Key and UNIQUE Key constraints, both are similar and enforce uniqueness of the column on which they are defined. Tag Archives: Modify existing table as System-Versioned Temporal Table. ALTER TABLE dbo.Product ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NULL, EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NULL, PERIOD FOR SYSTEMTIME (StartTime, EndTime). Let us drop the foreign key constraint using the following script.
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.
Look at the following two tables:
'Persons' table:
PersonID | LastName | FirstName | Age |
---|---|---|---|
1 | Hansen | Ola | 30 |
2 | Svendson | Tove | 23 |
3 | Pettersen | Kari | 20 |
'Orders' table:
OrderID | OrderNumber | PersonID |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 2 |
4 | 24562 | 1 |
Notice that the 'PersonID' column in the 'Orders' table points to the 'PersonID' column in the 'Persons' table.
The 'PersonID' column in the 'Persons' table is the PRIMARY KEY in the 'Persons' table.
The 'PersonID' column in the 'Orders' table is a FOREIGN KEY in the 'Orders' table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the 'PersonID' column when the 'Orders' table is created:
MySQL:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
![Keys Keys](/uploads/1/2/6/0/126068751/695763245.png)
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the 'PersonID' column when the 'Orders' table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
DROP CONSTRAINT FK_PersonOrder;
In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.
In simple words you can say that, a foreign key in one table used to point primary key in another table.
Let us take an example to explain it:
![Generated Always As Foriegn Key Generated Always As Foriegn Key](/uploads/1/2/6/0/126068751/847328671.png)
Here are two tables first one is students table and second is orders table.
Here orders are given by students.
First table:
S_Id | LastName | FirstName | CITY |
---|---|---|---|
1 | MAURYA | AJEET | ALLAHABAD |
2 | JAISWAL | RATAN | GHAZIABAD |
3 | ARORA | SAUMYA | MODINAGAR |
Second table:
O_Id | OrderNo | S_Id |
---|---|---|
1 | 99586465 | 2 |
2 | 78466588 | 2 |
3 | 22354846 | 3 |
4 | 57698656 | 1 |
Here you see that 'S_Id' column in the 'Orders' table points to the 'S_Id' column in 'Students' table.
- The 'S_Id' column in the 'Students' table is the PRIMARY KEY in the 'Students' table.
- The 'S_Id' column in the 'Orders' table is a FOREIGN KEY in the 'Orders' table.
The foreign key constraint is generally prevents action that destroy links between tables.
It also prevents invalid data to enter in foreign key column.
SQL FOREIGN KEY constraint ON CREATE TABLE:
(Defining a foreign key constraint on single column)
To create a foreign key on the 'S_Id' column when the 'Orders' table is created:
MySQL:
SQL Server /Oracle / MS Access:
SQL FOREIGN KEY constraint for ALTER TABLE:
If the Order table is already created and you want to create a FOREIGN KEY constraint on the 'S_Id' column, you should write the following syntax:
Defining a foreign key constraint on single column:
MySQL / SQL Server / Oracle / MS Access:
DROP SYNTAX for FOREIGN KEY COSTRAINT:
If you want to drop a FOREIGN KEY constraint, use the following syntax:
MySQL:
SQL Server / Oracle / MS Access:
Difference between primary key and foreign key in SQL:
These are some important difference between primary key and foreign key in SQL-
Primary key cannot be null on the other hand foreign key can be null.
Primary key is always unique while foreign key can be duplicated.
Generated Always As Identity
Primary key uniquely identify a record in a table while foreign key is a field in a table that is primary key in another table.
Generated Always As Row Start
There is only one primary key in the table on the other hand we can have more than one foreign key in the table.
By default primary key adds a clustered index on the other hand foreign key does not automatically create an index, clustered or non-clustered. You must manually create an index for foreign key.