Keys and Constraints

Article · 3:00

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT and CHECK - rules the database enforces so your code does not have to.

A constraint is a rule the server refuses to break. That is the whole argument for using them: application code can be bypassed by a migration script, a second application, an admin panel or somebody at a terminal, and a constraint cannot.

A primary key uniquely identifies a row, cannot be NULL, and in InnoDB determines the physical order the rows are stored in. A short, monotonically increasing key - a surrogate INT AUTO_INCREMENT - is therefore the sensible default, because inserts append rather than landing in the middle. A random UUID as the primary key is a known performance problem for exactly this reason; if you need one, store it in a separate unique column.

A foreign key says a value must exist in another table. It also decides what happens when the parent goes: ON DELETE RESTRICT refuses - the safe default; ON DELETE CASCADE deletes the children, right for genuinely owned rows like order lines, dangerous elsewhere; ON DELETE SET NULL keeps the child and forgets the parent.

UNIQUE prevents duplicates and permits multiple NULLs, because unknowns are not known to be equal. NOT NULL requires a value. DEFAULT supplies one. CHECK enforces a condition and is genuinely enforced from MySQL 8.0.16 - before that it was parsed and ignored, which is worth knowing when reading an old schema.

CREATE TABLE subscriptions (
  -- Short and increasing: InnoDB stores rows in primary key order, so
  -- inserts append instead of landing in the middle of the tree.
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

  customer_id   INT UNSIGNED NOT NULL,
  plan          ENUM('free','pro','team') NOT NULL DEFAULT 'free',
  seats         SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  started_on    DATE NOT NULL,
  ends_on       DATE NULL,
  external_ref  CHAR(36) NULL,

  -- One active subscription per customer. NULLs are allowed more than
  -- once, because unknown is not known to equal unknown.
  UNIQUE KEY uq_subscription_ref (external_ref),

  -- Genuinely enforced from 8.0.16. Before that, parsed and ignored.
  CONSTRAINT chk_seats       CHECK (seats BETWEEN 1 AND 500),
  CONSTRAINT chk_date_order  CHECK (ends_on IS NULL OR ends_on > started_on),

  CONSTRAINT fk_sub_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE CASCADE      -- a subscription cannot outlive its customer
    ON UPDATE CASCADE
) ENGINE = InnoDB;

-- Each of these is now refused by the server, not by your application:
-- INSERT INTO subscriptions (customer_id, seats, started_on)
--   VALUES (999999, 1, '2026-01-01');          -- no such customer
-- INSERT INTO subscriptions (customer_id, seats, started_on)
--   VALUES (1, 0, '2026-01-01');               -- seats out of range
-- INSERT INTO subscriptions (customer_id, started_on, ends_on)
--   VALUES (1, '2026-06-01', '2026-01-01');    -- ends before it starts