FOREIGN KEY — Ensures relationships between tables stay valid
CHECK — Validates that values meet a specific condition
Constraints are your last line of defense — they catch bad data that application code lets through.
Why Constraints Matter
Without Constraints
With Constraints
Duplicate emails in users table
UNIQUE index rejects duplicates
Orders reference nonexistent customers
FOREIGN KEY prevents orphaned rows
Negative salaries inserted
CHECK constraint validates ranges
Required fields left blank
NOT NULL prevents missing values
Application bug inserts garbage
Database rejects invalid data
NOT NULL
-- Column-level NOT NULL constraint
CREATE TABLE employees (
employee_id INTEGER NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20) -- NULL allowed (optional)
);
```sql
-- Attempting to insert NULL into a NOT NULL column fails
INSERT INTO employees (employee_id, first_name, last_name, email, phone)
VALUES (1, 'John', NULL, 'john@example.com', '555-0100');
-- ERROR: null value in column "last_name" violates NOT-null constraint
UNIQUE
-- Single-column UNIQUE constraint
CREATE TABLE users (
user_id INTEGER NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
username VARCHAR(50) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```sql
-- Multi-column UNIQUE constraint (composite)
CREATE TABLE course_enrollments (
enrollment_id INTEGER NOT NULL,
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
UNIQUE (student_id, course_id) -- A student can't enroll in the same course twice
);
PRIMARY KEY
-- Single-column primary key
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
```sql
-- Composite primary key
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);
```sql
-- Table-level constraint (same result as above)
CREATE TABLE employees (
employee_id INTEGER NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);
FOREIGN KEY
-- Foreign key referencing another table
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2),
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
```sql
-- Foreign key with ON DELETE behavior
CREATE TABLE order_items (
item_id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
CONSTRAINT fk_items_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE,
CONSTRAINT fk_items_product
FOREIGN KEY (product_id)
REFERENCES products(product_id)
ON DELETE RESTRICT
);
Referential Action
Behavior
CASCADE
Delete/update child rows when parent is deleted/updated
SET NULL
Set foreign key to NULL when parent is deleted
SET DEFAULT
Set foreign key to its default value
RESTRICT
Reject the delete/update if children exist
NO ACTION
Same as RESTRICT in most databases (deferred check)
CHECK
-- Column-level CHECK constraint
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) CHECK (salary > 0),
age INTEGER CHECK (age >= 18)
);
```sql
-- Named CHECK constraint
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
weight_kg DECIMAL(8,2),
status VARCHAR(20) NOT NULL,
CONSTRAINT chk_price_positive CHECK (price > 0),
CONSTRAINT chk_weight_positive CHECK (weight_kg > 0),
CONSTRAINT chk_status_valid CHECK (status IN ('Active', 'Discontinued', 'Pending'))
);
```sql
-- Multi-column CHECK constraint
CREATE TABLE reservations (
reservation_id INTEGER PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_dates_valid CHECK (end_date >= start_date)
);
DEFAULT
CREATE TABLE tickets (
ticket_id INTEGER PRIMARY KEY,
title VARCHAR(200) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'Open',
priority VARCHAR(10) NOT NULL DEFAULT 'Medium',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
created_by INTEGER NOT NULL
);
Adding Constraints to Existing Tables
-- Add NOT NULL constraint
ALTER TABLE employees
ALTER COLUMN phone SET NOT NULL;
```sql
-- Add UNIQUE constraint
ALTER TABLE employees
ADD CONSTRAINT uq_employees_email UNIQUE (email);
```sql
-- Add CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);
```sql
-- Add FOREIGN KEY constraint
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
```sql
-- Drop a constraint
ALTER TABLE employees
DROP CONSTRAINT chk_salary_positive;
Constraint Summary Table
Constraint
Purpose
NULL Allowed?
Multiple Per Table?
NOT NULL
Prevents NULL values
N/A (enforces non-NULL)
✅ (all columns)
UNIQUE
Ensures all values are different
Yes (one NULL)
✅
PRIMARY KEY
Uniquely identifies each row
❌ No
❌ One only
FOREIGN KEY
Ensures valid references
Yes
✅
CHECK
Validates a condition
Yes (NULL passes)
✅
DEFAULT
Provides a fallback value
N/A (used on INSERT)
✅
Best Practices
Practice
Why
Always define a PRIMARY KEY
Every table needs a reliable row identifier
Add NOT NULL to required fields
Prevent incomplete data at the source
Use CHECK constraints for business rules
Enforce ranges, enums, and logic in the database
Name your constraints
Makes error messages readable and debugging easier
Use FOREIGN KEY with ON DELETE behavior
Prevent orphaned rows and undefined behavior
Index foreign key columns
Improves JOIN performance and referential checks
Don't rely solely on application validation
Application code can be bypassed — constraints are the safety net