Constraints

Rules Written Into the Database

Constraints enforce integrity at the database level so invalid data can never be stored, no matter which application sends it.

The Constraint Toolbox

• PRIMARY KEY: unique identifier for every row; only one per table.
• FOREIGN KEY: value must exist in another table.
• UNIQUE: no duplicate values in the column(s).
• NOT NULL: column must always hold a value.
• CHECK: every row must satisfy an expression.
• DEFAULT: value automatically used when none given.

Building Constraints

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  total       DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status      VARCHAR(20) DEFAULT 'NEW',
  CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Foreign Key Actions

FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL

• CASCADE: deleting the parent deletes the children too.
• SET NULL: deleting the parent sets the FK column to NULL.
• RESTRICT/NO ACTION: the delete is blocked while children exist.

Key Points

• Constraints protect data at the source, not in the application.
• PRIMARY KEY + FOREIGN KEY drive relational integrity.
• CHECK, UNIQUE and NOT NULL cover the rest.
• Choose CASCADE, SET NULL or RESTRICT per relationship.