Up to this point, we have focused on the concept that a foreign key should link to the primary key of a table. In most cases, this makes sense as we use the foreign key to control the data that can be stored in the foreign key table as well as changes to the data in the primary key table. This approach is focused on referential integrity. 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 that is in the foreign key table.
There are some key similarities and differences between primary keys and foreign keys. Both a primary key and a foreign key can consist of more than one column. With a primary key, every value in a primary key column must be unique and have a value (not null). However, a foreign key by default could have a duplicate value in a one-to-many relationship and contain null values. One of the key aspects of the primary key is that each value is different.
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 does have 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. However, 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 have potentially 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