About
A Foreign Key Constraint is a constraint that creates a one to many relationship between a primary key and a foreign key (ie between two tables)
The foreign key constraint on a table A will :
- create a foreign key on the table A (from one column or a combination of columns)
- and link it to the primary key of a table B.
A foreign key constraint is also known as foreign key/primary key relationships
Referential Integrity is the data property whereby a foreign Key in one table has a corresponding Primary key in a different table.
Enforcement
A relational database management system (RDBMS) enforces a foreign key constraint :
- normally either by deleting the foreign key rows as well to maintain integrity,
- or by returning an error and not performing the delete.
Which method is used would be determined by the referential integrity constraint, as defined in the data dictionary.
Pro / cons
- Performance: An index lookup is executed for each insert/delete
Example
An Foreign Key example of a database (with the scott schema from Oracle).
Consider a part of the EMP (Employe) table.
EMPNO | ENAME | DPTNO |
---|---|---|
7369 | SMITH | 10 |
7499 | ALLEN | 20 |
7566 | JONES | 30 |
The column DPTNO from the EMP table refer to the column DEPTNO from the table DEPT (Department) below :
DEPTNO | DNAME |
---|---|
10 | ACCOUNTING |
20 | RESEARCH |
30 | DISTRIBUTION |
For each DPTNO in the table EMP, you must have a DPTNO in the table DEPT. We say then that :
- the column DPTNO from the table EMP is a foreign key.
- and then that the column DEPTNO is a primary key.
If it's not true, there is NO referential integrity.