Definition
SQL constraints are rules enforced by the database engine to maintain data accuracy and consistency. They guarantee that bad data can never enter the database — even if the application has bugs. The six standard constraint types: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. Constraints can be column-level (applied to one column) or table-level (applied to multiple columns). They are the SQL implementation of the relational model integrity rules.
Real-life analogy: The gatekeeper
Imagine a bank that only accepts deposits if the account number exists, the amount is positive, and the currency is valid. The bank teller (application) checks these — but the vault (database) has its own independent checks: a PRIMARY KEY enforcer ensures no duplicate account, a FOREIGN KEY enforcer checks the account exists, and a CHECK enforcer verifies the amount is positive. Even if the teller makes a mistake, the vault rejects invalid transactions.
All six constraint types
Complete constraint reference with all options
-- PRIMARY KEY: uniquely identifies each row, cannot be NULL
CREATE TABLE Employee (
EmpID INT PRIMARY KEY, -- Column-level constraint
...
);
-- Table-level PRIMARY KEY (required for composite PKs)
CREATE TABLE Enrollment (
StudentID INT NOT NULL,
CourseID VARCHAR(10) NOT NULL,
CONSTRAINT pk_enrollment PRIMARY KEY (StudentID, CourseID) -- Composite PK
);
-- FOREIGN KEY: references PK of another table — enforces referential integrity
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
DeptID INT CONSTRAINT fk_dept
REFERENCES Department(DeptID)
ON DELETE CASCADE -- Delete employee if dept deleted
ON UPDATE CASCADE, -- Update DeptID if parent changes
ManagerID INT REFERENCES Employee(EmpID) -- Self-referencing FK
);
-- ON DELETE / ON UPDATE options:
-- CASCADE: propagate change to child rows
-- SET NULL: set FK to NULL in child rows (requires FK column to allow NULL)
-- SET DEFAULT: set FK to default value in child rows
-- RESTRICT / NO ACTION: prevent delete/update if child rows exist (default)
-- UNIQUE: all values in column must be distinct (NULLs allowed, except in SQL Server)
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE, -- Single column unique
TaxID VARCHAR(20),
Phone VARCHAR(15),
UNIQUE (TaxID, Phone) -- Composite unique constraint
);
-- NOT NULL: value must be provided (cannot be NULL)
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
Name VARCHAR(200) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
Description TEXT NULL -- Explicitly allows NULL (default anyway)
);
-- CHECK: custom business rule expression
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Age INT CHECK (Age BETWEEN 18 AND 65),
Salary DECIMAL(10,2) CHECK (Salary > 0),
Status VARCHAR(20) CHECK (Status IN ('Active','Inactive','Terminated')),
JoinDate DATE,
LeaveDate DATE,
-- Table-level check (cross-column validation)
CONSTRAINT chk_dates CHECK (LeaveDate IS NULL OR LeaveDate > JoinDate)
);
-- DEFAULT: provide a value when none is specified
CREATE TABLE Order_ (
OrderID SERIAL PRIMARY KEY,
Status VARCHAR(20) DEFAULT 'Pending',
CreatedAt TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
IsActive BOOLEAN DEFAULT TRUE,
Priority INT DEFAULT 5 CHECK (Priority BETWEEN 1 AND 10)
);
-- Adding constraints to existing tables (ALTER TABLE)
ALTER TABLE Employee ADD CONSTRAINT chk_salary CHECK (Salary > 0);
ALTER TABLE Employee ADD CONSTRAINT uq_email UNIQUE (Email);
ALTER TABLE Employee ADD CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES Department(DeptID);
ALTER TABLE Employee ALTER COLUMN Name SET NOT NULL;
-- Dropping constraints
ALTER TABLE Employee DROP CONSTRAINT chk_salary;
ALTER TABLE Employee ALTER COLUMN MiddleName DROP NOT NULL;
-- Disabling constraints temporarily (for bulk loads)
ALTER TABLE Order_ DISABLE TRIGGER ALL; -- PostgreSQL (disables FK checks via triggers)
ALTER TABLE Employee NOCHECK CONSTRAINT ALL; -- SQL ServerDeferrable constraints (PostgreSQL)
Deferred constraint checking for complex operations
-- Problem: inserting circular FK references in one transaction
-- Employee.ManagerID references Employee.EmpID
-- Cannot insert (1, manager=2) before (2, manager=NULL) — FK violation!
-- Solution: DEFERRABLE INITIALLY DEFERRED
ALTER TABLE Employee
ADD CONSTRAINT fk_manager
FOREIGN KEY (ManagerID) REFERENCES Employee(EmpID)
DEFERRABLE INITIALLY DEFERRED; -- Check only at COMMIT time, not per statement
-- Now within a transaction:
BEGIN;
INSERT INTO Employee (EmpID, Name, ManagerID) VALUES (2, 'Manager', NULL); -- OK: no manager
INSERT INTO Employee (EmpID, Name, ManagerID) VALUES (1, 'Staff', 2); -- OK: manager is row we just inserted
COMMIT; -- FK checked here — both rows exist now, so constraint satisfied
-- Deferred vs immediate:
-- INITIALLY IMMEDIATE (default): checked after each statement
-- INITIALLY DEFERRED: checked at COMMIT
-- Can override within transaction: SET CONSTRAINTS fk_manager DEFERRED;Practice questions
- Difference between PRIMARY KEY and UNIQUE constraint: (Answer: PRIMARY KEY = UNIQUE + NOT NULL + only one per table. UNIQUE allows NULL values (multiple in most DBMS), multiple UNIQUE constraints per table allowed. Both create an index automatically.)
- ON DELETE CASCADE vs ON DELETE RESTRICT: (Answer: CASCADE: automatically deletes child rows when parent is deleted. RESTRICT/NO ACTION: prevents deletion of parent if child rows exist — raises an error. Use CASCADE for dependent records, RESTRICT for independent records.)
- Can a table have multiple FOREIGN KEYS? (Answer: Yes — a table can have any number of foreign keys referencing the same or different tables. Example: Orders table has FK to Customers, FK to Products, FK to Employees (salesperson).)
- CHECK (Status IN ('Active','Inactive')) — what happens if you insert Status = 'active' (lowercase)? (Answer: Constraint violation — SQL string comparison is case-sensitive. 'active' != 'Active'. To handle both cases: CHECK (UPPER(Status) IN ('ACTIVE','INACTIVE')).)
- Why is DEFAULT CURRENT_TIMESTAMP useful in audit tables? (Answer: Automatically records the exact timestamp when each row was inserted without requiring the application to provide it. Combined with a separate UpdatedAt column and a BEFORE UPDATE trigger, you get a complete audit trail.)
On LumiChats
LumiChats can audit your table definitions for missing constraints, suggest the right FK behaviour (CASCADE vs RESTRICT) for your use case, and generate ALTER TABLE statements to add constraints to existing tables safely.
Try it free