Does a foreign key need to reference a primary key or candidate key?
database, foreign-keys, primary-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.
Problem
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?