Concepts and theorems · Intermediate

Referential integrity: preventing references to missing rows

Distinguish foreign-key validation, delete actions, null handling, and indexing when connecting related tables.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. What a foreign key asserts
  2. Following a customer and an order
  3. Deletion requires an explicit policy
  4. Constraints are not indexes or authorization
  5. Ask Logeia about this articleCheck your understanding

What a foreign key asserts

Referential integrity preserves a relationship between reference values and existing keys in another table. If an order customer_id identifies a customer, a foreign key can reject an order that refers to a nonexistent customer. Naming columns similarly does not establish this database guarantee. The constraint must identify the referencing columns and the referenced candidate key. The referenced key also needs the uniqueness properties required by the database. This turns a relationship described only in application conventions into a rule checked when relevant data changes.

Following a customer and an order

Suppose the customers table contains id=10 but no id=99. An order referencing 10 satisfies the relationship; an order referencing 99 does not. Whether the order identifier is unique is a separate constraint. A primary or unique key identifies rows within its own table, while the foreign key checks a relationship between tables. For a composite reference, the tuple of column values matters rather than each value independently. Some constraints can be deferred under explicitly configured rules, so also check when validation occurs within a transaction.

Deletion requires an explicit policy

Deleting a referenced customer while orders remain requires a policy. The database can reject the deletion or perform a configured action such as cascading deletion or setting a reference to null. CASCADE can remove more related data than a caller expects, while SET NULL must be compatible with the column nullability and other constraints. Choose the action from the business meaning of the relationship, not merely to make an error disappear. In particular, preserving historical orders can require a different lifecycle design from automatically deleting every dependent record.

Constraints are not indexes or authorization

A foreign key checks integrity; it is not a universal promise that every referencing column receives an automatic performance index. In PostgreSQL, indexing the referencing side is a separate design decision that can matter for lookups and referenced-row changes. Existence is also different from authorization: a row can exist while the current user has no right to act on it. Nullable references need explicit interpretation as well. If a relationship is mandatory, combine the foreign key with appropriate nullability rules rather than assuming the reference constraint alone rejects every absent reference.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 11

Choose a learning path