SQL Keys Overview

In SQL, keys are used to maintain referential integrity among relations. Put simply, this means keys allow tables to reference each other, and each reference will be “correct” every time. Referential integrity also prevents records from being “dangled” or “orphaned” by another record that has been deleted.

Primary Keys and Foreign Keys: The Basics

As an example, let’s consider an office employee roster with two tables.

The first table contains a list of departments and their IDs. Perhaps “billing” has ID #1, “marketing” has ID #2, and “accounting” has ID #3. The column for these unique IDs would be a PRIMARY KEY.

The second table, then, is a list of employees, with several columns: one each for the employee’s first and last names, one for their employee ID, and another for the ID of the employee’s department. The employee ID is another PRIMARY KEY. The column representing the employee’s department ID is called a FOREIGN KEY and links the values in that column to the department IDs in the department table. Let’s say Betty Smith works in billing, John Brown and Samantha Jones work in marketing, and David Parker works in accounting. The creation of these tables is shown in Figure 1.

Please note PostgreSQL does not use the FOREIGN KEY constraint in the CREATE TABLE command, as shown in Figure 1, presumably because the developers consider mentioning both FOREIGN KEY and REFERENCES to be being redundant. However, many other SQL databases require the explicit use of the FOREIGN KEY keyword.

A simple JOIN operation could be used, then, to determine in which department each employee works, without having to manually interpret the department ID number. This is shown in Figure 2.

This is easy enough to do, but there are some problems that arise from having one table reference another table. The most immediate problem, in this case, would be to determine what would happen if, all of a sudden, the billing department were to be deleted? Would Betty Smith just vanish? Fortunately, the SQL:2003 standard defined five different ways by which this situation can be handled. These are called referential actions.

{qbapagebreak title=SQL Referential Actions}

Referential Actions

The five referential actions determine what action a database takes when a reference is deleted or updated in a way that would affect other data. This section briefly examines each action, using the employees and departments tables above as an example.

Cascade

In a CASCADE action, everything that happens to the referenced key also happens to the referent. In our example, if the billing department were deleted, Betty Smith would also be deleted.

Restrict

The RESTRICT action tells the database to flag any update or delete operations as illegal if dependent rows exist. In this case, neither Betty Smith nor the billing department would be deleted, and the database would tell the user an illegal operation occurred.

No Action

NO ACTION tells the database to not perform any actions, in a similar manner to the RESTRICT action. However, if triggers are present, they may still be executed in a way that does not compromise the referential integrity of the database. Triggers are covered separately in this series of articles.

Set Null

The SET NULL action causes referent fields to be set to NULL if the underlying FOREIGN KEY is updated or deleted. This action requires the referent column to allow NULL values.

Set Default

The SET DEFAULT action operates in a similar manner to SET NULL, but instead of setting the value to NULL, the database sets the value to the column’s default value. An example of setting a default value is shown in Figure 3.