PostgreSQL Constraints

Constraints are rules applied to table columns that enforce the validity and integrity of data. They prevent invalid data from being inserted or updated in a table. Instead of relying entirely on application code to validate input, constraints place the rules directly in the database — ensuring that data is correct regardless of which application or user modifies it.

Types of Constraints

NOT NULL Constraint

NOT NULL ensures a column always has a value. A NULL represents the absence of a value — NOT NULL says that absence is not permitted.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    department VARCHAR(50)
);

-- This will fail
INSERT INTO employees (name, email) VALUES (NULL, 'alice@example.com');
-- ERROR: null value in column "name" violates not-null constraint

UNIQUE Constraint

UNIQUE ensures no two rows can have the same value in the specified column.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(150) UNIQUE
);

-- First insert succeeds
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- This will fail — duplicate email
INSERT INTO users (username, email) VALUES ('alice2', 'alice@example.com');
-- ERROR: duplicate key value violates unique constraint

Multi-Column UNIQUE Constraint

A UNIQUE constraint can span multiple columns. The combination of values must be unique, but individual column values may repeat.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)
);

-- A student can enroll in many courses, and a course can have many students
-- But a student cannot be enrolled in the same course twice

PRIMARY KEY Constraint

A primary key uniquely identifies every row in a table. It combines NOT NULL and UNIQUE. Each table can have only one primary key, but that key can consist of multiple columns (composite primary key).

-- Single column primary key
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Using SERIAL as the primary key type creates an auto-incrementing integer — a common pattern for surrogate keys.

FOREIGN KEY Constraint

A foreign key links a column in one table to the primary key of another, enforcing referential integrity. A row cannot be inserted in the child table if the referenced value does not exist in the parent table.

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    dept_id INT REFERENCES departments(id)
);

-- This fails if department 99 does not exist
INSERT INTO employees (name, dept_id) VALUES ('Alice', 99);
-- ERROR: insert or update on table "employees" violates foreign key constraint

ON DELETE Behavior

When a referenced row in the parent table is deleted, the foreign key constraint defines what happens to the child rows:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    dept_id INT REFERENCES departments(id) ON DELETE SET NULL
);

CHECK Constraint

A CHECK constraint ensures that values in a column satisfy a specified condition. Any Boolean expression can be used.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC CHECK (price >= 0),
    discount NUMERIC CHECK (discount BETWEEN 0 AND 100),
    stock INT CHECK (stock >= 0)
);

-- This will fail
INSERT INTO products (name, price) VALUES ('Pen', -5.00);
-- ERROR: new row for relation "products" violates check constraint

Named CHECK Constraint

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT CONSTRAINT age_range CHECK (age BETWEEN 18 AND 65)
);

Named constraints produce clearer error messages and can be dropped by name.

DEFAULT Constraint

DEFAULT provides a fallback value for a column when none is provided in an INSERT statement.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders DEFAULT VALUES;
-- status = 'pending', created_at = current timestamp

Adding and Removing Constraints on Existing Tables

Add a Constraint

-- Add NOT NULL
ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

-- Add UNIQUE
ALTER TABLE employees ADD CONSTRAINT uq_employee_email UNIQUE (email);

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

Drop a Constraint

ALTER TABLE employees DROP CONSTRAINT uq_employee_email;
ALTER TABLE products DROP CONSTRAINT chk_price;

Viewing Constraints

-- In psql
\d table_name

-- Or query information_schema
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'employees';

Example: Fully Constrained Table

CREATE TABLE registrations (
    id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    age INT CHECK (age >= 16 AND age <= 100),
    course VARCHAR(100) NOT NULL,
    registered_at TIMESTAMP DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended'))
);

This table enforces: required fields, a valid email uniqueness, age range, and a restricted set of status values — all at the database level.

Key Points

• Constraints enforce data validity rules directly in the database, independent of application code.
• NOT NULL prevents missing values; UNIQUE prevents duplicate values.
• PRIMARY KEY combines NOT NULL and UNIQUE to identify each row.
• FOREIGN KEY links tables and enforces referential integrity; ON DELETE controls behavior when parent rows are removed.
• CHECK allows custom conditions using any Boolean expression.
• DEFAULT provides automatic fallback values for columns.
• Constraints can be added or removed from existing tables using ALTER TABLE.