Constraints

Constraints are rules the database enforces on every insert and update, no matter which application, script or person writes the data. They're your last and strongest line of defence against duplicates, missing values and impossible data like negative prices.

NOT NULL

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  nickname TEXT
);

INSERT INTO members (id, name, nickname) VALUES (1, NULL, 'Ace');

Error: NOT NULL constraint failed: members.name

A NOT NULL column must always have a value. Make columns NOT NULL by default, and allow NULL only where "unknown" or "not applicable" is a real possibility.

UNIQUE

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT UNIQUE
);

INSERT INTO accounts (username, email) VALUES ('sam', 'sam@example.com');
INSERT INTO accounts (username, email) VALUES ('sam', 'other@example.com');

Error: UNIQUE constraint failed: accounts.username

No two rows may share a value. Most databases allow multiple NULLs in a unique column (each unknown value is considered different); SQL Server allows only one.

Multi-column UNIQUE

CREATE TABLE enrolments (
  student_id INTEGER NOT NULL,
  course_code TEXT NOT NULL,
  term TEXT NOT NULL,
  UNIQUE (student_id, course_code, term)
);

INSERT INTO enrolments VALUES (1, 'SQL101', '2026-spring'), (1, 'SQL101', '2026-autumn');

SELECT * FROM enrolments;

The combination must be unique: a student can take the same course in different terms, but not twice in the same term.

CHECK

INSERT INTO products (name, category, price) VALUES ('Broken Price', 'Toys', -10);

Error: CHECK constraint failed: price >= 0

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  capacity INTEGER CHECK (capacity > 0),
  CHECK (ends_on >= starts_on)
);

INSERT INTO events (name, starts_on, ends_on, capacity) VALUES ('Conference', '2026-05-10', '2026-05-12', 300);
SELECT * FROM events;

A CHECK can test a single column or compare several (a table constraint, written after the columns). A check that evaluates to NULL passes, which is why capacity can still be left empty. MySQL has enforced CHECK constraints only since version 8.0.16.

DEFAULT

INSERT INTO products (name, category, price) VALUES ('Tea Towel', 'Home', 6.50);

SELECT name, stock FROM products WHERE name = 'Tea Towel';

Strictly speaking DEFAULT isn't a constraint, but it's declared alongside them: stock INTEGER NOT NULL DEFAULT 0.

Naming constraints

CREATE TABLE payments (
  id INTEGER PRIMARY KEY,
  amount DECIMAL(10, 2) NOT NULL,
  CONSTRAINT positive_amount CHECK (amount > 0)
);

INSERT INTO payments (amount) VALUES (0);

Error: CHECK constraint failed: positive_amount

Named constraints produce clearer error messages and are easier to drop or change later (ALTER TABLE payments DROP CONSTRAINT positive_amount in most databases).

Constraints vs application checks

Applications should validate input too — to give users friendly messages. But only the database sees every write: imports, admin scripts, other services, and two requests racing each other. Use both: validation for user experience, constraints for guarantees.

Try it yourself

Create a table for discount codes with sensible rules, then try to break each rule.