Integrity Constraints

Integrity constraints are rules about valid data that the database enforces *itself*, rejecting any insert, update, or delete that would break them. You never have to trust application code to keep the data clean — the DBMS checks automatically on every change. Three kinds do most of the work: entity integrity, referential integrity, and domain constraints.

The three core rules

1. 1Entity integrity — every row must be identifiable: its primary key must be unique and never NULL.
2. 2Referential integrity — links between tables must be real: a foreign key must reference a row that actually exists in the other table (or be NULL).
3. 3Domain constraint — each column only accepts sensible values from its allowed type or set (an age must be a non-negative number).

A worked example — orders and customers

CREATE TABLE Customer (
  cust_id INT PRIMARY KEY,           -- entity integrity: unique, not NULL
  name    VARCHAR(60) NOT NULL
);
CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  cust_id  INT REFERENCES Customer(cust_id),  -- referential integrity
  amount   INT CHECK (amount >= 0)             -- domain constraint
);

What happens when you delete a referenced row

If you delete customer 42 while orders still point at them, referential integrity would break. The DBMS follows the referential action you declared: RESTRICT blocks the delete, CASCADE deletes the dependent orders too, and SET NULL clears the foreign key to mean 'not linked'.