Relational Data Modeling - Foreign Key Constraint (one-to-many relationship)

1 - 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.

3 - 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.

4 - 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

5 - Example

An Foreign Key example of a database (with the scott schema from Oracle).

Consider a part of the EMP (Employe) table.

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 :


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.

6 - Documentation

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap