Relational Theory for Computer Professionals

Chapter 3. Keys, Foreign Keys, and Related Matters

Keep breathing!—that’s the key.

—Gimli the Dwarf, in Peter Jackson’s film The Two Towers (2002), based on the book of the same name by J. R. R. Tolkien (1954)

Every relvar has a key (sometimes more than one), and some relvars have foreign keys as well. This chapter explains these observations and explores some of their implications.

Integrity constraints

Every database is subject to certain integrity constraints (constraints for short), which update operations must never be permitted to violate. Here are some examples of constraints that might apply to the suppliers-and-parts database:

• Supplier status values must be in the range 1 to 100 inclusive.

• Part weights must be greater than zero.

• London suppliers must have status 20.

• Red parts must be stored in London.

• Suppliers with status less than 20 aren’t allowed to supply part P6.

And so on. Now, one particular kind of constraint that applies to every relvar is what’s called a key constraint, which means, loosely, that some combination of the attributes of the relvar in question serves as a unique identifier—a key—for tuples in that relvar. For example, every tuple in relvar S at any given time t has a supplier number (i.e., SNO value) that’s unique, meaning it’s different from the SNO value in every other tuple appearing in that same relvar at that same time t. Thus, tuples in relvar S at any given time are uniquely identified by supplier number. Likewise, tuples in relvar P are uniquely identified ...