- Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
- It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
- For example, a table called Player has a primary key called Player_ID. Another table called PlayerDetails has a foreign key which references Player_ID in order to uniquely identify the relationship between both the tables.
- A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
- FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created. CREATE TABLE Orders
(O_Id int PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int REFERENCES Persons (P_Id));
The "Persons" table:
The "Orders" table:
Primary key of one relation is used as an attribute in another relation is called as foreign key.
It is a part of referential integrity where one relation is referenced in another relation.
Consider two relations as:
emp(empNo, EN, FN, email)
Dept(DeptNo, Name, Location)
To establish a relation between emp and Dept, one may have to refer the primary key of Dept in emp.
So now new relation can be written as:
emp (empNo, FN, LN, email, DNo)
Here 'DNo' is a foreign key.
This referential integrity can be shown in a diagramatic way as:
Foreign key also helps in retrieving related information from two related tables.
For eg. one may retrieve the department of any employee using foreign ey 'Dno'