Eric Campbell 5 min read Databases

SQL Constraints: Primary Key, Foreign Key, Unique, Check

Constraints are rules enforced by your database engine that guarantee data quality and consistency. Unlike application-level validation that can be bypassed, constraints operate at the database layer...

Key Insights

  • Constraints enforce data integrity at the database level, preventing invalid data from entering your tables—they’re your first line of defense against data corruption and should be preferred over application-level validation alone.
  • Foreign keys with CASCADE options automate related data cleanup, but use them carefully: CASCADE DELETE can unexpectedly wipe out large amounts of data if you’re not deliberate about your schema design.
  • Name your constraints explicitly rather than accepting auto-generated names—you’ll thank yourself when debugging production issues at 2 AM and need to quickly identify which constraint is failing.

Understanding SQL Constraints

Constraints are rules enforced by your database engine that guarantee data quality and consistency. Unlike application-level validation that can be bypassed, constraints operate at the database layer and apply regardless of how data enters the system—whether through your application, a batch import, or direct SQL queries.

Here’s the difference between tables with and without constraints:

-- Without constraints: anything goes
CREATE TABLE users_bad (
    id INT,
    email VARCHAR(255),
    age INT
);

-- With constraints: data integrity enforced
CREATE TABLE users_good (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 18 AND age < 150)
);

The second table prevents duplicate IDs, ensures email uniqueness, forbids NULL emails, and validates age ranges. All of this happens automatically, every time data is inserted or updated.

Primary Key Constraints

A primary key uniquely identifies each row in a table. Every table should have one. The database enforces two rules for primary keys: values must be unique, and they cannot be NULL.

Single-column primary keys are most common:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Composite primary keys use multiple columns, typically for junction tables in many-to-many relationships:

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

This ensures each product appears only once per order. The primary key is the combination of both columns.

Try inserting duplicate primary key values and the database rejects it:

INSERT INTO customers (customer_id, name) VALUES (1, 'Alice');
INSERT INTO customers (customer_id, name) VALUES (1, 'Bob');
-- ERROR: duplicate key value violates unique constraint "customers_pkey"

Choose primary keys carefully. Auto-incrementing integers work well for most cases. UUIDs provide global uniqueness but consume more space. Natural keys (like email addresses) seem appealing but can create problems when values need to change.

Foreign Key Constraints

Foreign keys establish relationships between tables and enforce referential integrity. They ensure that a value in one table corresponds to an existing value in another table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Now you cannot create an order for a non-existent customer:

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (1, 999, '2024-01-15', 150.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- DETAIL: Key (customer_id)=(999) is not present in table "customers"

Foreign keys become powerful with CASCADE options that automate related data management:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

With ON DELETE CASCADE, deleting a customer automatically deletes all their orders. With ON UPDATE CASCADE, updating a customer’s ID propagates to all their orders.

Use CASCADE DELETE cautiously. It’s appropriate for truly dependent data (order items belong to orders), but dangerous for loosely coupled relationships. Consider ON DELETE RESTRICT (the default) or ON DELETE SET NULL for cases where you want to preserve historical records.

Unique Constraints

UNIQUE constraints ensure column values are distinct across all rows, similar to primary keys but with key differences: tables can have multiple UNIQUE constraints, and UNIQUE columns can contain NULL values (in most databases, multiple NULLs are allowed).

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE
);

This table ensures no two users share the same username or email, while allowing users without phone numbers:

INSERT INTO users (user_id, username, email, phone)
VALUES (1, 'alice', 'alice@example.com', NULL);

INSERT INTO users (user_id, username, email, phone)
VALUES (2, 'bob', 'bob@example.com', NULL);
-- This succeeds: multiple NULLs are allowed

INSERT INTO users (user_id, username, email, phone)
VALUES (3, 'charlie', 'alice@example.com', '555-1234');
-- ERROR: duplicate key value violates unique constraint "users_email_key"

UNIQUE constraints can also span multiple columns:

CREATE TABLE product_inventory (
    warehouse_id INT,
    product_sku VARCHAR(50),
    quantity INT,
    UNIQUE (warehouse_id, product_sku)
);

This ensures each product appears only once per warehouse, while allowing the same product in different warehouses.

Check Constraints

CHECK constraints validate data against custom rules using boolean expressions. They’re perfect for enforcing business rules that other constraints can’t handle.

Simple range validation:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 70),
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

Restricting values to a specific set:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Cross-column validation ensures logical consistency:

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CHECK (end_date >= start_date)
);

INSERT INTO events (event_id, event_name, start_date, end_date)
VALUES (1, 'Conference', '2024-06-01', '2024-05-28');
-- ERROR: new row violates check constraint "events_check"

CHECK constraints have limitations. They can’t reference other tables or use subqueries. For complex validation requiring data from multiple tables, consider triggers or application-level validation.

Combining Constraints Effectively

Real-world tables combine multiple constraint types. Here’s a realistic example:

CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_type VARCHAR(20) NOT NULL CHECK (plan_type IN ('free', 'basic', 'premium')),
    start_date DATE NOT NULL DEFAULT CURRENT_DATE,
    end_date DATE,
    monthly_price DECIMAL(8, 2) CHECK (monthly_price >= 0),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'paused', 'cancelled')),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    UNIQUE (user_id, plan_type),
    CHECK (end_date IS NULL OR end_date > start_date),
    CONSTRAINT valid_price_for_plan CHECK (
        (plan_type = 'free' AND monthly_price = 0) OR
        (plan_type != 'free' AND monthly_price > 0)
    )
);

Notice the explicit constraint naming for the complex CHECK constraint. This makes debugging easier.

Constraint naming conventions matter:

CONSTRAINT pk_subscriptions PRIMARY KEY (subscription_id)
CONSTRAINT fk_subscriptions_users FOREIGN KEY (user_id) REFERENCES users(user_id)
CONSTRAINT uk_subscriptions_user_plan UNIQUE (user_id, plan_type)
CONSTRAINT ck_subscriptions_dates CHECK (end_date > start_date)

Use prefixes: pk_ for primary keys, fk_ for foreign keys, uk_ for unique constraints, ck_ for check constraints.

Managing Constraints on Existing Tables

You’ll often need to add constraints to existing tables. Use ALTER TABLE:

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

-- Add a foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers 
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

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

Dropping constraints requires knowing their names (another reason to name them explicitly):

ALTER TABLE orders
DROP CONSTRAINT fk_orders_customers;

Some databases allow temporarily disabling constraints for bulk operations:

-- PostgreSQL
ALTER TABLE orders DISABLE TRIGGER ALL;
-- Bulk insert operations
ALTER TABLE orders ENABLE TRIGGER ALL;

-- MySQL
SET FOREIGN_KEY_CHECKS=0;
-- Bulk operations
SET FOREIGN_KEY_CHECKS=1;

Use this feature carefully and only when necessary. Disabling constraints removes your safety net.

Constraints add minimal overhead for individual operations but provide enormous value. They catch bugs before they corrupt data, document your schema’s business rules, and enable the database to optimize queries. Define them early, name them clearly, and let your database do what it does best: protect your data.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.