Foreign and Primary Keys

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:

• The column data should be unique.
• The key can consist of multiple columns.
• It should not contain any null values.

There are other instances where this can also work to link to a one-to-zero/one relationship. This case is rare but possible. One example could be the employee and benefits table that we discussed in a prior tutorial. As a reminder, in the Employee table, we would have information specific to the employee such as their name, address, position, and date hired. In the benefits table, you could have the investment plan, medical plan, dental plan, and life insurance plan information. The employee could only have one benefit record. However, it is also possible that the employee will not have chosen a benefit plan at all so not all employees will have a correlating benefits record.

---

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.