SQL Constraints: Ensuring Data Integrity by Design - Complete Guide

SQL constraints are database-enforced rules that automatically validate data as it's inserted, updated, or deleted, preventing invalid states before they can corrupt your data. Unlike application-level validation, constraints work at the storage layer, catching errors even when data comes from imports, APIs, or direct database access. This guide covers all major constraint types with practical examples and production schema design patterns. 

What Are Constraints and Why They Matter

Constraints define business rules and data quality requirements directly in your database schema. They include rules like "no duplicate emails," "salary must be positive," or "order must belong to an existing customer." When violated, the database rejects the operation with a clear error.

Benefits include:

• Automatic enforcement: No code changes needed across applications
• Performance: Validation happens at the storage engine level
• Consistency: Same rules apply regardless of data source
• Bug prevention: Catches edge cases application logic might miss

PRIMARY KEY and UNIQUE Constraints

PRIMARY KEY: Row Identity

A PRIMARY KEY uniquely identifies each row and cannot be NULL. Most tables have exactly one PRIMARY KEY.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2)
);

UNIQUE Constraint: Uniqueness Without Being Primary

UNIQUE constraints enforce uniqueness but allow NULL (in most databases). Perfect for business identifiers like email addresses.

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE,
  username VARCHAR(50) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

user_id	email	username
1	rahul@email.com	rahul_p
2	NULL	guest_user
3	priya@email.com	priya_s

Attempting to insert duplicate email or username fails with a clear constraint violation error.

NOT NULL and DEFAULT Constraints

NOT NULL prevents NULL values in essential columns. DEFAULT provides fallback values when none specified.

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  status ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending',
  order_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Inserting an order without specifying status uses 'pending'. Trying to set customer_id to NULL fails immediately.

order_id	customer_id	status	created_at
1	101	pending	2025-01-05 14:30:00

FOREIGN KEY and Referential Integrity

FOREIGN KEY constraints ensure referenced records exist, maintaining referential integrity across tables.

-- Parent table
CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);

-- Child table with FOREIGN KEY
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  total_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

FOREIGN KEY Action Options

• ON DELETE CASCADE: Delete child records when parent deleted
• ON DELETE SET NULL: Set foreign key to NULL (requires nullable column)
• ON DELETE RESTRICT / NO ACTION: Prevent parent deletion if children exist
• ON UPDATE CASCADE: Update child keys when parent key changes

Trying to insert order with non-existent customer_id fails. Deleting a customer with orders triggers the specified action.

CHECK Constraints for Business Rules

CHECK constraints enforce domain-specific business rules directly in the database.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  salary DECIMAL(10,2) CHECK (salary > 0 AND salary <= 1000000),
  age INT CHECK (age >= 18 AND age <= 100),
  department VARCHAR(50) CHECK (department IN ('IT', 'HR', 'Finance', 'Sales')),
  is_active BOOLEAN DEFAULT TRUE
);

Invalid salary, age outside range, or unknown department all fail at INSERT/UPDATE time with precise error messages.

Designing Robust Schemas with Constraints

Complete Production-Ready Example

CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) CHECK (price > 0),
  stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
  category_id INT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(category_id)
    ON DELETE SET NULL
);

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT CHECK (quantity > 0),
  unit_price DECIMAL(10,2) CHECK (unit_price > 0),
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

Constraint Strategy: Database First

1. Define PRIMARY KEY and NOT NULL for all required columns
2. Add UNIQUE for business identifiers (email, SKU, invoice_number)
3. Implement FOREIGN KEYs with appropriate cascade actions
4. Add CHECK constraints for domain validation
5. Use DEFAULT values for common patterns

Trade-offs and Best Practices

Constraint Type	Pros	Cons	When to Use
PRIMARY KEY	Fast lookups, prevents duplicates+NULL	Requires unique values	Every table needs one
FOREIGN KEY	Enforces relationships	Slower writes, cascade complexity	Production relational data
CHECK	Precise business rules	Limited portability, complex expressions	Critical domain validation
NOT NULL + DEFAULT	Prevents missing data	Minimal overhead	All essential columns

Constraints shift validation burden from fragile application code to bulletproof database engines, dramatically reducing data corruption risks and maintenance costs. Start with strict constraints during design, then selectively relax only when proven necessary through production monitoring and analytics.