SQL Keys

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 inmarketing, 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.

SQL Keys