SQL • 2026-06-12

SQL Constraints: Complete Guide to Data Integrity

Master SQL constraints including PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT with practical examples

SQL Constraints

📋 Table of Contents

Introduction to Constraints
PRIMARY KEY Constraint
FOREIGN KEY Constraint
UNIQUE Constraint
NOT NULL Constraint
CHECK Constraint
DEFAULT Constraint
Constraint Management
Best Practices

Introduction to Constraints

SQL Constraints are rules enforced on data columns to maintain data integrity and accuracy.

┌─────────────────────────────────────────────────────────┐
│                  SQL CONSTRAINTS                         │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  PRIMARY KEY  →  Unique identifier for each row         │
│  FOREIGN KEY  →  Link between tables                    │
│  UNIQUE       →  Ensure column values are unique        │
│  NOT NULL     →  Prevent NULL values                    │
│  CHECK        →  Validate data against condition        │
│  DEFAULT      →  Set default value for column           │
│                                                          │
└─────────────────────────────────────────────────────────┘

Why Use Constraints?

Benefit	Description
Data Integrity	Ensures data accuracy and consistency
Validation	Prevents invalid data entry
Relationships	Maintains referential integrity between tables
Performance	Indexes created automatically for some constraints
Documentation	Self-documents business rules

PRIMARY KEY Constraint

PRIMARY KEY uniquely identifies each record in a table. Cannot contain NULL values.

Creating PRIMARY KEY

-- Method 1: Inline definition
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL
);

-- Method 2: Table-level constraint
CREATE TABLE customers (
    customer_id INT,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (customer_id)
);

-- Method 3: Named constraint
CREATE TABLE customers (
    customer_id INT,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);

-- With AUTO_INCREMENT (MySQL)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL
);

Composite PRIMARY KEY

-- Multiple columns as primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Named composite primary key
CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE NOT NULL,
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);

Adding PRIMARY KEY to Existing Table

-- Add primary key
ALTER TABLE customers
ADD PRIMARY KEY (customer_id);

-- Add named primary key
ALTER TABLE customers
ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id);

-- Drop primary key
ALTER TABLE customers
DROP PRIMARY KEY;

FOREIGN KEY Constraint

FOREIGN KEY creates a link between two tables, ensuring referential integrity.

Creating FOREIGN KEY

-- Basic foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Named foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE NOT NULL,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
);

CASCADE Options

-- ON DELETE CASCADE: Delete child records when parent is deleted
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- ON DELETE SET NULL: Set foreign key to NULL when parent is deleted
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- ON DELETE RESTRICT: Prevent deletion if child records exist (default)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

CASCADE Options Comparison

Option	Parent Deleted	Parent Updated	Use Case
CASCADE	Delete children	Update children	Dependent data (order items)
SET NULL	Set FK to NULL	Update FK	Optional relationships
RESTRICT	Prevent deletion	Prevent update	Protect data integrity
NO ACTION	Same as RESTRICT	Same as RESTRICT	Default behavior
SET DEFAULT	Set to default	Set to default	Fallback value

Multiple Foreign Keys

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    CONSTRAINT fk_order FOREIGN KEY (order_id) 
        REFERENCES orders(order_id) ON DELETE CASCADE,
    CONSTRAINT fk_product FOREIGN KEY (product_id) 
        REFERENCES products(product_id) ON DELETE RESTRICT
);

UNIQUE Constraint

UNIQUE ensures all values in a column are different. Allows one NULL value.

Creating UNIQUE Constraint

-- Inline unique
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

-- Table-level unique
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100),
    username VARCHAR(50),
    UNIQUE (email),
    UNIQUE (username)
);

-- Named unique constraint
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100),
    username VARCHAR(50),
    CONSTRAINT uq_email UNIQUE (email),
    CONSTRAINT uq_username UNIQUE (username)
);

Composite UNIQUE Constraint

-- Multiple columns must be unique together
CREATE TABLE course_schedule (
    schedule_id INT PRIMARY KEY,
    course_id INT,
    day_of_week VARCHAR(10),
    time_slot TIME,
    UNIQUE (course_id, day_of_week, time_slot)
);

Managing UNIQUE Constraints

-- Add unique constraint
ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email);

-- Drop unique constraint
ALTER TABLE users
DROP CONSTRAINT uq_email;

NOT NULL Constraint

NOT NULL ensures a column cannot have NULL values.

Creating NOT NULL Constraint

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)  -- Allows NULL
);

Managing NOT NULL Constraints

-- Add NOT NULL (MySQL)
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(20) NOT NULL;

-- Add NOT NULL (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN phone SET NOT NULL;

-- Remove NOT NULL (MySQL)
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(20) NULL;

-- Remove NOT NULL (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN phone DROP NOT NULL;

CHECK Constraint

CHECK validates data against a specified condition.

Creating CHECK Constraint

-- Basic check constraint
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    CHECK (price > 0),
    CHECK (stock >= 0)
);

-- Named check constraint
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_stock CHECK (stock >= 0)
);

Complex CHECK Constraints

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    age INT NOT NULL,
    hire_date DATE NOT NULL,
    -- Multiple conditions
    CONSTRAINT chk_salary CHECK (salary > 0 AND salary <= 1000000),
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_email CHECK (email LIKE '%@%.%'),
    CONSTRAINT chk_hire_date CHECK (hire_date >= '2000-01-01')
);

Managing CHECK Constraints

-- Add check constraint
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);

-- Drop check constraint
ALTER TABLE products
DROP CONSTRAINT chk_price;

DEFAULT Constraint

DEFAULT sets a default value for a column when no value is specified.

Creating DEFAULT Constraint

-- SQL Server: Disable constraint ALTER TABLE orders NOCHECK CONSTRAINT fk_customer; -- Enable constraint ALTER TABLE orders CHECK CONSTRAINT fk_customer;

-- PostgreSQL: Disable triggers (affects constraints) ALTER TABLE orders DISABLE TRIGGER ALL; ALTER TABLE orders ENABLE TRIGGER ALL;


---

## Best Practices

### 1. Always Use PRIMARY KEY

```sql
-- ✅ GOOD: Every table has a primary key
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

-- ❌ BAD: No primary key
CREATE TABLE users (
    username VARCHAR(50) NOT NULL
);

2. Name Your Constraints

-- ✅ GOOD: Named constraints for easy management
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
);

3. Use Appropriate CASCADE Options

-- ✅ GOOD: CASCADE for dependent data
CREATE TABLE order_items (
    order_id INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE  -- Delete items when order is deleted
);

-- ✅ GOOD: RESTRICT for independent data
CREATE TABLE orders (
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT  -- Prevent customer deletion if orders exist
);

4. Validate Data with CHECK Constraints

-- ✅ GOOD: Enforce business rules at database level
CREATE TABLE products (
    price DECIMAL(10,2),
    discount_percent DECIMAL(5,2),
    stock INT,
    CHECK (price > 0),
    CHECK (discount_percent >= 0 AND discount_percent <= 100),
    CHECK (stock >= 0)
);

5. Use NOT NULL for Required Fields

-- ✅ GOOD: Required fields are NOT NULL
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20)  -- Optional
);

Summary

This guide covered:

✅ PRIMARY KEY for unique identification
✅ FOREIGN KEY for referential integrity
✅ UNIQUE for preventing duplicates
✅ NOT NULL for required fields
✅ CHECK for data validation
✅ DEFAULT for default values
✅ Constraint management operations
✅ Best practices for data integrity

Key Takeaways:

Use PRIMARY KEY on every table
Implement FOREIGN KEY for relationships
Choose appropriate CASCADE options
Validate data with CHECK constraints
Name constraints for easy management
Use NOT NULL for required fields
Set sensible DEFAULT values