SQL Constraints – PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK

What Are Constraints?

A constraint is a rule attached to a column or table that the database engine enforces on every INSERT and UPDATE. If a statement would violate a constraint, it is rejected with an error. Constraints are defined in CREATE TABLE or added later with ALTER TABLE.

Constraints can be inline (defined on the column) or table-level (defined after all columns, required for multi-column constraints).

SQL

-- Inline constraint (on the column definition)
email VARCHAR(255) NOT NULL UNIQUE

-- Named table-level constraint (after all columns)
CONSTRAINT uq_users_email UNIQUE (email)

💡

Name your constraints

Unnamed constraints get auto-generated names like users_ibfk_1 that are hard to reference later when dropping or modifying them. Use CONSTRAINT constraint_name with a clear naming convention, e.g. fk_orders_user_id, uq_users_email, chk_products_price_positive.

PRIMARY KEY

The primary key uniquely identifies each row. It implicitly enforces NOT NULL and UNIQUE. A table can have only one primary key (but it can span multiple columns — a composite PK).

SQL

-- Single-column primary key (inline)
CREATE TABLE users (
  id    INT           PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255)  NOT NULL
);

-- Composite primary key (table-level — required for multi-column)
CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL DEFAULT 1,
  CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

FOREIGN KEY

A foreign key creates a link between two tables — the value in the FK column must either match a value in the referenced primary key column, or be NULL. This is called referential integrity.

SQL

CREATE TABLE orders (
  id         INT  PRIMARY KEY AUTO_INCREMENT,
  user_id    INT  NOT NULL,
  total      DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

The ON DELETE and ON UPDATE clauses control what happens when the referenced row in the parent table changes:

Action	Effect on child rows
`CASCADE`	Automatically delete/update child rows to match
`SET NULL`	Set the FK column to NULL in child rows
`RESTRICT`	Refuse the parent delete/update if child rows exist (default in most DBs)
`NO ACTION`	Similar to RESTRICT; checked at end of statement (PostgreSQL)
`SET DEFAULT`	Set FK column to its default value (limited support)

UNIQUE

A UNIQUE constraint ensures that all values in a column (or column combination) are distinct across all rows. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and a unique column allows multiple NULL values (since NULL ≠ NULL in SQL).

SQL

-- Inline UNIQUE
CREATE TABLE users (
  id       INT         PRIMARY KEY AUTO_INCREMENT,
  email    VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(50)  NOT NULL UNIQUE
);

-- Named UNIQUE constraint (table-level)
CREATE TABLE products (
  id  INT PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(50) NOT NULL,
  CONSTRAINT uq_products_sku UNIQUE (sku)
);

-- Composite UNIQUE (unique combination, not each column individually)
CREATE TABLE team_memberships (
  user_id INT NOT NULL,
  team_id INT NOT NULL,
  CONSTRAINT uq_team_membership UNIQUE (user_id, team_id)
);

NOT NULL

NOT NULL is the simplest constraint — it prevents a column from storing NULL. By default, all columns allow NULL unless you explicitly add NOT NULL.

SQL

CREATE TABLE employees (
  id         INT           PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(100)  NOT NULL,   -- required
  last_name  VARCHAR(100)  NOT NULL,   -- required
  manager_id INT,                      -- optional — NULL allowed (top-level managers)
  notes      TEXT                      -- optional — NULL allowed
);

-- Attempting to insert without a NOT NULL column fails:
INSERT INTO employees (last_name) VALUES ('Smith');
-- ERROR: Column 'first_name' cannot be null

DEFAULT

The DEFAULT constraint specifies a value to use when the column is omitted from an INSERT statement. It does not prevent NULL — it only provides a fallback when no value is supplied.

SQL

CREATE TABLE posts (
  id         INT           PRIMARY KEY AUTO_INCREMENT,
  title      VARCHAR(255)  NOT NULL,
  status     VARCHAR(20)   NOT NULL DEFAULT 'draft',
  view_count INT           NOT NULL DEFAULT 0,
  created_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Omitting status and view_count uses their defaults:
INSERT INTO posts (title) VALUES ('My First Post');
-- Result: status='draft', view_count=0, created_at=NOW()

CHECK

A CHECK constraint enforces a custom condition on column values. The row is rejected if the condition evaluates to FALSE (it passes if TRUE or NULL).

SQL

CREATE TABLE employees (
  id         INT           PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100)  NOT NULL,
  salary     DECIMAL(10,2) NOT NULL,
  status     VARCHAR(20)   NOT NULL DEFAULT 'active',
  birth_date DATE,
  CONSTRAINT chk_salary_positive
    CHECK (salary >= 0),
  CONSTRAINT chk_status_valid
    CHECK (status IN ('active', 'inactive', 'terminated')),
  CONSTRAINT chk_birth_date
    CHECK (birth_date IS NULL OR birth_date < CURRENT_DATE)
);

-- This insert fails the CHECK constraint:
INSERT INTO employees (name, salary) VALUES ('Alice', -5000);
-- ERROR: Check constraint 'chk_salary_positive' is violated.

ℹ️

CHECK constraint support by database

PostgreSQL fully enforces CHECK constraints. MySQL 8.0.16+ enforces them — earlier MySQL versions parsed CHECK syntax but silently ignored it. SQLite 3.25+ enforces CHECK constraints. Always verify your database version supports CHECK enforcement if you rely on it for data integrity.

Summary

📋 Summary

PRIMARY KEY: unique + NOT NULL, one per table. Use composite PKs for junction tables.
FOREIGN KEY: enforces referential integrity between tables. ON DELETE CASCADE auto-removes orphaned child rows; RESTRICT (default) prevents deletion of referenced rows.
UNIQUE: no duplicate values in the column (or column combination). Allows multiple NULLs.
NOT NULL: column must always have a value. The most common constraint after PRIMARY KEY.
DEFAULT: fallback value when column is omitted from INSERT. Does not prevent NULL if you explicitly insert NULL.
CHECK: custom condition. Fully supported in PostgreSQL, MySQL 8.0.16+, SQLite 3.25+.
Name your constraints with CONSTRAINT name for easier maintenance.

FAQ

What is the difference between UNIQUE and PRIMARY KEY? +

Both enforce uniqueness, but there are three key differences. (1) A table can have only one PRIMARY KEY, but multiple UNIQUE constraints. (2) PRIMARY KEY implies NOT NULL — a null value is never allowed. UNIQUE allows multiple NULL values because NULL != NULL in SQL logic. (3) The PRIMARY KEY is used as the clustered index in MySQL InnoDB and is the default join target for FOREIGN KEY references. UNIQUE constraints create secondary indexes. In most cases, use PRIMARY KEY for the main row identifier and UNIQUE for natural keys like email addresses.

What happens when ON DELETE CASCADE is triggered? +

When you delete a row in the parent table, the database automatically deletes all child rows in the table with the CASCADE foreign key. For example, if you delete a user with id = 42, and orders.user_id has ON DELETE CASCADE, all orders with user_id = 42 are also deleted. This cascade can chain — if order_items also has ON DELETE CASCADE on order_id, the items are deleted too. This is powerful but can cause large unintended deletes. Use RESTRICT when you want the database to refuse the parent delete if child rows exist.

Can a FOREIGN KEY reference a UNIQUE column instead of a PRIMARY KEY? +

Yes — a FOREIGN KEY can reference any column (or combination of columns) that has a UNIQUE constraint, not just the PRIMARY KEY. For example, you could have orders.user_email referencing users.email where email has a UNIQUE constraint. In practice, it is almost always better to reference the primary key (an integer ID) because integer comparisons are faster and IDs never change. Referencing email means if a user changes their email, you must update all FK references too.

Should I enforce business rules with CHECK constraints or in the application? +

Both. Database-level constraints are the last line of defence — they cannot be bypassed regardless of how data enters the database (via the application, a migration script, a direct SQL query, a third-party tool). Application-level validation gives better error messages and can enforce complex rules that span multiple tables. A good rule of thumb: put simple, column-level rules (non-negative salary, valid status enum) in CHECK constraints; put complex multi-table or business-logic rules in the application. Never rely solely on the application for data integrity.