written 8.3 years ago by |
FOREIGN KEY:
- 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:
P_Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Hansen | Ola | Timoteivn 10 | Sandnes |
2 | Svendson | Tove | Borgvn 23 | Sandnes |
3 | Pettersen | Kari | Storgt 20 |
The "Orders" table:
O_Id | OrderNo | P_Id |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 2 |
4 | 24562 | 1 |
written 24 months ago by |
Foreign Key:
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'