Up to this point, we have focused on the concept that a foreign key should link to the primary key of another table. We use the foreign key to control the data that can be stored in the foreign key table, as well as to control changes to the data in the primary key table. This constraint enforces referential integrity by guaranteeing that changes cannot be made to the data in the primary key table if the changes invalidate the link to the data in the foreign key table.
There are some similarities and some differences between primary keys and foreign keys. Both a primary key and a foreign key can consist of more than one column. However, every value in a primary key column must be unique and have a value (not null). A foreign key, on the other hand, can have a duplicate value in a one-to-many relationship and can contain null values. A foreign key can also be temporarily disabled to simplify dropping tables or moving data, and they can be added after tables have been created to avoid having to generate them in the right order.
If we did not want to link a foreign key to a primary key, the other alternative is to link it to a column that has a unique constraint on it. Note that a unique constraint can consist of NULL values unless a NOT NULL constraint is set. It is important to note that the foreign key has to reference a unique column, at the very least. As such, a foreign key could be linked to a candidate key. A candidate key is not a specific type of constraint that can be set in a database. Instead, it is a set of attributes that can uniquely identify a record in a table. A table could have multiple candidate keys, with one being set as the primary key for a table. However, any of those candidate keys could potentially have been selected as the primary key. The candidate key would have all of the constraints similar to a primary key but it may not be the best option to select as the primary key. The properties of the candidate key include:
Source: Authored by Vincent Tran