Foreign Keys


A foreign key is a column, or group of columns, that refer to a primary key.

  • data types of the foreign and primary keys must be the same
  • does not have to be unique
  • values may be NULL
  • value that is not NULL must match some value of the referenced primary key

Composite Foreign Keys

  • foreign key that refers to a composite primary key must also be composite
  • All columns of a composite foreign key value must either:
    • match some primary key value
    • or be NULL

Special Cases

  • Multiple foreign keys may refer to the same primary key
  • foreign key may refer to the primary key of the same table

Foreign Key Constraint

  • foreign key constraint is created with a foreign key clause in the CREATE TABLE statement
  • syntax: FOREIGN KEY (ColumnName) REFERENCES TableName (ColumnName)
  • usually follows all column definitions, but can be created at any point in the CREATE TABLE statement

Syntax

CREATE TABLE TableName (

…
FOREIGN KEY (ColumnName) REFERENCES TableName (ColumnName),
…
);