Search code examples
databaseforeign-keysprimary-key

Does a foreign key need to reference a primary key or candidate key?

I am having two conflicting definitions of foreign key.

From Wikipedia

the foreign key is defined in a second table, but it refers to the primary key in the first table.

From my lecture notes:

Foreign key does not have to match a primary key but must match a candidate key in some relation

Which is which? Does a foreign key need to reference a primary key or candidate key?

Solution

  • In the relational model of data, a foreign key must reference a candidate key.

    In almost all SQL dbms, a foreign key must reference a candidate key.

    In MySQL, a foreign key can reference just about anything.

    Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL.

    Emphasis added.

    This is a Bad Thing, IMHO.