2.4Data constraints and integrity

Even after normalizing and decomposing tables, a database quickly loses integrity without constraint design that correctly maintains the relationships between tables and prevents invalid values from creeping in. A database designer must judge what a given column should uniquely identify, under what conditions a given reference can break, and whether application-side validation alone is sufficient or the DBMS itself should also enforce constraints. This section covers the three pillars of primary keys, foreign keys, and check constraints as design judgments for maintaining integrity.

2.4.1Primary keys and candidate keys

• A candidate key is an attribute (or set of attributes) that uniquely identifies each row of a table, and is a minimal set—removing even one attribute from it would break the uniqueness. A single table can have multiple candidate keys (for example, in an employee table, both employee ID and email address can each independently uniquely identify a row—both are candidate keys).
• A primary key is the one candidate key, among possibly several, chosen to represent the table's identifier. References from other tables (foreign keys) use this primary key. A candidate key not chosen as the primary key is called an alternate key, and its uniqueness is typically still enforced separately, via a UNIQUE constraint or similar.

2.4.2Foreign keys and referential constraints

• A foreign key is a column that references another table's primary key (or candidate key). A referential constraint (referential integrity constraint) requires that a foreign-key value either matches an existing primary-key value in the referenced table, or is NULL. This prevents a "child record pointing to a nonexistent parent record" from creeping in.
• The behavior when a referenced row is deleted or updated must be explicitly designed as a referential action: CASCADE (cascade the delete/update to the child), RESTRICT/NO ACTION (refuse to delete/update the parent while a child still references it), or SET NULL (set the child's foreign key to NULL). Which to choose depends on the business requirement—whether the child should survive or be removed when the parent is deleted.

2.4.3Check constraints (CHECK) and other integrity constraints

• A check constraint (CHECK) enforces an arbitrary condition a column (or row) must satisfy, at the DBMS level (for example, "stock quantity must be at least 0" or "discount rate must be between 0 and 100"). Validating only at the application layer cannot prevent invalid values from entering through another route (a batch job, direct SQL execution, and so on), so also enforcing the same constraint at the DBMS level, as a second line of defense, matters in practice.
• NOT NULL constraints disallow a missing value in a given column. Uniqueness constraints (UNIQUE) require only uniqueness on a non-primary-key column (such as an alternate key). These affect the schema's structure less than primary and foreign keys do, but they play the same role as referential and check constraints: mechanically guaranteeing, at the DBMS level, business rules that should not tolerate missing or duplicate values.

A database designer at an e-commerce company is designing the foreign-key constraints between the "order" table and the "order line" table. Since the business requirement is "if an order is canceled, the order lines tied to it may be automatically deleted as well (a line has no meaning existing on its own)," the order-line foreign key (order ID) is set to CASCADE, so that deleting the parent order cascades the deletion to its lines. Between the "customer" table and the "order" table, however, the requirement is "even if a customer is deregistered, past order history must be retained for audit and accounting purposes," so the order's foreign key (customer ID) should instead be set to RESTRICT (or SET NULL). If CASCADE were mistakenly used here too, deleting a customer would instantly destroy that customer's entire order history—a serious incident that erases records required for accounting audits. Furthermore, this designer had been validating the business rule "an order line's quantity must be at least 1" only at the application layer, until one day a batch bulk-registration script bypassed that validation, inserting directly via SQL, and a fault occurred: invalid order lines with quantity 0 crept in. The lesson is that the correct response is to build a second line of defense—in addition to application-layer validation, also add a check constraint (CHECK) of "quantity >= 1" on the order-line table's quantity column at the DBMS level—so that invalid values cannot creep in regardless of which route the write comes through. Designing a foreign key's referential action and a check constraint therefore both require first identifying every route through which invalid data could creep in, and then judging each case individually against the business rules.

2.4.4Section summary

• Candidate key is a minimal, uniquely identifying attribute set. Primary key is the representative chosen among them (the rest become alternate keys)
• Foreign key plus referential constraint prevents a child record from pointing to a nonexistent parent. The referential action (CASCADE/RESTRICT/SET NULL) is chosen based on the business requirement
• A check constraint (CHECK) is a second line of defense against invalid values entering through routes—batch jobs, direct SQL, and so on—that application-layer validation alone cannot cover