SQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT

A constraint is a rule the database enforces on every insert and update. Constraints stop bad data at the door: a duplicate email, a negative balance, an order for a customer who does not exist. Application code has bugs; constraints are the last line of defence.

The six common constraints#

We will use this table for the examples:

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  balance    INTEGER NOT NULL DEFAULT 0 CHECK (balance >= 0)
);

DEFAULT#

When an insert leaves a column out, its DEFAULT is used:

INSERT INTO accounts (account_id, email)
VALUES (1, 'asha@example.com');

SELECT * FROM accounts;

account_id | email            | balance
-----------+------------------+--------
1          | asha@example.com | 0

Common defaults are 0, 'pending', FALSE, and the current time (DEFAULT CURRENT_TIMESTAMP).

NOT NULL#

email is required:

INSERT INTO accounts (account_id) VALUES (1);

ERROR: NOT NULL constraint failed: accounts.email

Make a column NOT NULL whenever a row makes no sense without it. Most columns should be.

UNIQUE#

No two accounts can share an email:

INSERT INTO accounts VALUES (1, 'asha@example.com', 100);
INSERT INTO accounts VALUES (2, 'asha@example.com', 50);

ERROR: UNIQUE constraint failed: accounts.email

The first insert succeeds; the second is rejected. A UNIQUE column may still contain NULLs in most databases, because one unknown value is not considered equal to another (SQL Server allows only one NULL).

A unique constraint can cover several columns together: UNIQUE (student_id, course_id) allows a student in many courses and a course with many students, but not the same pair twice.

CHECK#

CHECK enforces any condition on the row:

INSERT INTO accounts VALUES (1, 'asha@example.com', -20);

ERROR: CHECK constraint failed: balance >= 0

More examples:

CHECK (price > 0)
CHECK (status IN ('pending', 'shipped', 'delivered'))
CHECK (end_date >= start_date)

PRIMARY KEY#

A primary key uniquely identifies every row: it is UNIQUE and NOT NULL, and a table can have only one. It can be a single column, or several columns together (a composite key):

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id  INTEGER,
  PRIMARY KEY (student_id, course_id)
);

Databases automatically create an index for every primary key, so looking rows up by it is fast.

FOREIGN KEY#

A foreign key makes sure a reference points to something real:

CREATE TABLE teams (
  team_id INTEGER PRIMARY KEY,
  name    VARCHAR(50) NOT NULL
);

CREATE TABLE players (
  player_id INTEGER PRIMARY KEY,
  name      VARCHAR(50),
  team_id   INTEGER REFERENCES teams(team_id)
);

INSERT INTO teams VALUES (1, 'Red');
INSERT INTO players VALUES (1, 'Asha', 1);   -- team 1 exists: OK
INSERT INTO players VALUES (2, 'Ravi', 9);   -- team 9 does not exist

ERROR: FOREIGN KEY constraint failed

The same protection works in the other direction: deleting team 1 while Asha still belongs to it also fails.

What should happen when the parent is deleted?#

You choose with ON DELETE:

team_id INTEGER REFERENCES teams(team_id) ON DELETE SET NULL

ON UPDATE works the same way when a parent key changes.

Naming constraints#

Give constraints names so error messages are clear and they are easy to drop later:

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  price      DECIMAL(10, 2) NOT NULL,
  CONSTRAINT chk_products_price_positive CHECK (price > 0)
);

Common mistakes#

Practice#

CREATE TABLE reviews (
  review_id  INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(product_id),
  user_id    INTEGER NOT NULL,
  rating     INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment    TEXT,
  UNIQUE (product_id, user_id)
);

Key takeaway#