Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

Foreign and Primary Keys

Author: Sophia

what's covered
This tutorial explores if foreign keys can only derive from primary keys in two parts:
  1. Introduction
  2. Alternative Links for Foreign Keys

1. Introduction

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.

2. Alternative Links for Foreign Keys

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:

  • 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, which is rare but possible. One example could be the employee and benefits tables that we discussed in a prior tutorial. As a reminder, the employee table would have information specific to the employee, such as their name, address, position, and date hired. The benefits table would have the investment plan, medical plan, dental plan, and life insurance plan information. Each employee would only have one benefit record. However, it is possible that an employee would not have any correlating benefits record, if they had not yet chosen a benefit plan.


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.

summary
There are unique circumstances where a foreign key may not derive from a primary key.

Source: Authored by Vincent Tran