Foreign key

Definition

A foreign key is a set of one or more columns in a relationship that refers to a primary key in another relationship.

All foreign key values appear in another relationship as key values.

  • This is a referential integrity constraint.

  • By convention, the foreign key of a relationship is preceded (or followed) by the # symbol in a relationship schema

Example

Let's consider the following relationship diagrams:

Customer (NumCl, NomCl, AdrCl) Refers to all customers.

Order (NCmde, DateCmde, #NumCl) Refers to all orders.

The NumCl attribute in the Order relationship (table) is a foreign key.

It takes its values from the value domain of the NumCl attribute in the Customer relationship diagram.

An order is always placed by an existing Customer in the database.

An attribute can be both a primary and a foreign key:

Film (numFilm, title, year).

Actor (numActor, last name, first name).

Casting (#numFilm, #numActor, character).

Data modelling process