SQL constraints

Rules the database enforces to keep your data valid and consistent.

Core types

  • NOT NULL – column must have a value.
  • CHECK – boolean rule must be true.
    MySQL: enforced from 8.0.16+ (InnoDB).
  • UNIQUE – no duplicate values (can be multi-column).
  • PRIMARY KEY – unique + not null (usually 1 per table; can be composite).
  • FOREIGN KEY – value must exist in another table; can define actions on delete/update.
  • DEFAULT – value used when none supplied (not a “constraint” in the strictest sense, but used with them).

Extras: Postgres has EXCLUDE constraints and DEFERRABLE constraints.

Create table (inline vs table-level, with names)

Example (MySQL / Postgres)

CREATE TABLE departments (
  dept_id   SERIAL PRIMARY KEY,           -- PG: SERIAL; MySQL: BIGINT AUTO_INCREMENT
  dept_code VARCHAR(10) UNIQUE NOT NULL,
  name      VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  emp_id     SERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL,
  salary     DECIMAL(12,2) NOT NULL CHECK (salary >= 0),
  dept_id    INT,

  CONSTRAINT uq_employee_email UNIQUE (email),
  CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id)
    REFERENCES departments(dept_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

Notes

  • MySQL: use AUTO_INCREMENT instead of SERIAL if you prefer; ensure InnoDB engine for FKs.
  • Postgres: SERIAL or GENERATED ALWAYS AS IDENTITY.

Altering constraints

-- Add
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);

-- Drop
ALTER TABLE employees DROP CONSTRAINT chk_salary;         -- Postgres
ALTER TABLE employees DROP INDEX uq_employee_email;       -- MySQL UNIQUE index
ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept;       -- MySQL FK

-- Rename (PG)
ALTER TABLE employees RENAME CONSTRAINT uq_employee_email TO uq_emp_email;

Common FK actions

... FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE RESTRICT     -- or NO ACTION: block delete
    ON UPDATE CASCADE;     -- propagate key change
-- Alternatives: ON DELETE CASCADE | SET NULL | SET DEFAULT (PG)

Choose carefully: CASCADE is powerful but can remove lots of rows.

Inserts that demonstrate constraints

-- OK
INSERT INTO departments (dept_code, name) VALUES ('HR', 'Human Resources');

-- UNIQUE violation
INSERT INTO departments (dept_code, name) VALUES ('HR', 'Another HR'); -- fails

-- FK violation
INSERT INTO employees (email, salary, dept_id)
VALUES ('a@b.com', 1000, 9999); -- fails if dept 9999 doesn’t exist

-- CHECK violation
INSERT INTO employees (email, salary) VALUES ('x@y.com', -1); -- fails

Partial/conditional uniqueness

  • Postgres: use a partial index:

CREATE UNIQUE INDEX uq_user_email_active ON users(email) WHERE is_active = true;

  • SQL Server: filtered unique index (similar idea).

  • MySQL: emulate via composite UNIQUE + default sentinel (or application logic).

Postgres-only niceties

  • DEFERRABLE (check at commit, not per statement):

ALTER TABLE employees
  ADD CONSTRAINT uq_emp_email UNIQUE (email) DEFERRABLE INITIALLY DEFERRED;

  • EXCLUDE (e.g., prevent overlapping time ranges):

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE meetings (
  room int, ts tstzrange,
  EXCLUDE USING gist (room WITH =, ts WITH &&)
);

Listing constraints quickly

  • Postgres

SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'employees';

  • MySQL

SHOW CREATE TABLE employees;
-- or
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.table_constraints
WHERE table_name='employees' AND table_schema=DATABASE();

Best practices

  • Name your constraints (pk_, fk_, uq_, chk_) for clear errors.
  • Keep PKs simple and stable (surrogate keys often easier).
  • Index FK columns for join performance.
  • Use CHECK for domain rules (non-negative amounts, valid enums).
  • Choose FK actions explicitly; avoid accidental cascades.
  • Validate existing data before adding constraints to legacy tables.
  • Don’t confuse constraints with indexes: UNIQUE creates an index; others don’t necessarily—create supporting indexes as needed.
Back to blog

Leave a comment