Keys and Constraints

Constraints are your last line of defence#

Application code has bugs. Someone will run a manual script. Another service will write to your database. Constraints are rules the server enforces regardless of who is writing. If a rule matters, put it in the schema.

Primary key and AUTO_INCREMENT#

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  status      ENUM('new','paid','shipped','cancelled') NOT NULL DEFAULT 'new',
  ordered_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

A primary key is unique and NOT NULL. AUTO_INCREMENT hands out the next number. Two things surprise people: values are not reused after deletes or rolled-back inserts, so gaps are normal, and the counter lives per table. Never rely on IDs being gapless.

Foreign keys#

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

orders.customer_id ──────▶ customers.id
   (child)                    (parent)

INSERT orders with customer_id = 999  ▶ rejected, no parent
DELETE customers id = 1 (has orders)  ▶ rejected by RESTRICT

The actions:

• RESTRICT / NO ACTION: block the parent change.
• CASCADE: apply the change to children (delete the parent, delete the children).
• SET NULL: null the child column (the column must allow NULL).

I default to RESTRICT. A careless ON DELETE CASCADE turns one wrong DELETE into a wiped order history.

Foreign keys need matching types: INT UNSIGNED against INT fails with a confusing error 3780 or 1215.

UNIQUE, NOT NULL, DEFAULT#

ALTER TABLE products ADD UNIQUE KEY uq_products_code (code);

UNIQUE allows multiple NULLs, since NULL is "unknown" and two unknowns aren't equal. A composite unique key works too: UNIQUE (order_id, product_id) stops the same product being added twice to one order.

NOT NULL should be your default for columns. Allow NULL only when "unknown" genuinely means something different from empty.

CHECK constraints#

ALTER TABLE products
  ADD CONSTRAINT chk_price CHECK (price >= 0),
  ADD CONSTRAINT chk_stock CHECK (stock >= 0);

In MySQL 8.0.16 and later, checks are enforced. In older versions they were silently parsed and ignored, so confirm your version.

Seeing it fail on purpose#

INSERT INTO products (name, category, price, stock)
VALUES ('Bad', 'misc', -5, 1);
-- ERROR 3819: Check constraint 'chk_price' is violated.

Run failing statements deliberately. It's the fastest way to learn what each error looks like.

Common mistakes#

• Skipping foreign keys "for performance". The cost is usually small; bad data costs more.
• Assuming UNIQUE blocks duplicate NULLs.
• Using CASCADE everywhere without thinking about what gets deleted.
• Mismatched types between parent and child columns.

Practice#

Create order_items(order_id, product_id, qty, unit_price) with a composite primary key, two foreign keys, and a CHECK that qty > 0. Then try to insert: a row with a missing order, a row with qty 0, and a duplicate (order_id, product_id). Record the exact error number for each.