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

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

• sharding: Extracting data with foreigns keys is hard and therefore sharding is hard
• 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.

The column DPTNO from the EMP table refer to the column DEPTNO from the table DEPT (Department) below :

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.

Documentation

• Thoughts on Foreign Keys in Github?