Integrity Constraints: Domain, Entity & Referential

Three rules that keep the data honest

The relational model is not just tables — its third component is data integrity: rules the DBMS enforces so the data stay meaningful. Three constraints carry the exam:

### 1. Domain constraint

A domain is the set of all legal values an attribute may take — its data type, size, and any allowable range or set. A domain constraint requires every value in a column to come from that one domain. grade ∈ {A, B, C, D, F, null}; gpa is a numeric(3,2) between 0.00 and 4.00. Domain constraints are how CHECK, data types, and ranges encode business rules.

### 2. Entity integrity

The entity-integrity rule states: no primary-key attribute (or any component of a composite primary key) may be null. The reasoning is airtight — a null primary key would be an *unidentifiable* row you could never reliably retrieve or reference. For a composite key, every component must be non-null; if even one part is null the row cannot be uniquely identified.

### 3. Referential integrity

A referential-integrity constraint states: every foreign-key value must either match an existing primary-key value in the referenced relation or be null. This is what prevents *orphan* rows — an order pointing at a customer who does not exist.

Worked example: what referential integrity forbids

Given ORDERS(order_id, customer_id, ...) where customer_id references CUSTOMERS:

CUSTOMERS:  1 Student Union   2 Alumni Office   3 Athletics Dept
ORDERS:     order 99 -> customer_id = 7     <-- ILLEGAL: no customer 7
            order 98 -> customer_id = NULL  <-- legal only if the FK is optional

The first row violates referential integrity; the DBMS rejects the insert. The second is legal only if the relationship is optional (FK nullable).

Deleting a referenced row: three options

When you delete a CUSTOMERS row that orders still reference, the DBMS must do *something* with those children. The three standard options — written into the foreign-key definition — are:

- RESTRICT (NO ACTION) — block the delete until the child rows are gone first. Preserves history; needs manual cleanup. - CASCADE — delete the parent and automatically delete every dependent child. Convenient, but it can silently destroy sales history. - SET NULL — delete the parent and set the foreign key to null in the orphaned children. Keeps the child rows but loses the link (only valid if the FK is nullable).

Which one is correct is a *business* decision, not a technical one — data-retention rules drive the choice.