UNIQUE & CHECK Constraints

While primary and foreign keys handle identity and relationships, UNIQUE and CHECK constraints provide additional control over what data is allowed in your tables. UNIQUE ensures no duplicates for a given column or combination of columns, and CHECK lets you define custom validation rules directly in the database schema.

UNIQUE Constraints

A UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct. Unlike PRIMARY KEY, a UNIQUE column can contain NULL values.

Single-Column UNIQUE

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT UNIQUE,
  email TEXT UNIQUE,
  display_name TEXT
);

Now PostgreSQL will reject any insert or update that would create a duplicate username or email:

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('alice', 'bob@example.com');
-- ERROR: duplicate key value violates unique constraint "users_username_key"
-- DETAIL: Key (username)=(alice) already exists.

UNIQUE vs. PRIMARY KEY

In standard SQL, a UNIQUE column allows multiple NULLs because NULL is considered distinct from every other NULL (NULL != NULL). PostgreSQL follows this behavior:

INSERT INTO users (username, email) VALUES (NULL, 'a@example.com');
INSERT INTO users (username, email) VALUES (NULL, 'b@example.com');
-- Both succeed: two NULLs in a UNIQUE column are allowed

If you want a UNIQUE column that also disallows NULLs, combine both constraints:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

Multi-Column UNIQUE Constraints

You can enforce uniqueness across a combination of columns using a table-level constraint:

CREATE TABLE room_bookings (
  id SERIAL PRIMARY KEY,
  room_number INTEGER NOT NULL,
  booking_date DATE NOT NULL,
  time_slot TEXT NOT NULL,
  booked_by TEXT,
  UNIQUE (room_number, booking_date, time_slot)
);

This allows the same room to be booked on different dates or different time slots on the same date, but prevents double-booking the same room at the same time on the same date:

INSERT INTO room_bookings (room_number, booking_date, time_slot, booked_by)
VALUES (101, '2025-04-15', 'morning', 'Alice');

INSERT INTO room_bookings (room_number, booking_date, time_slot, booked_by)
VALUES (101, '2025-04-15', 'afternoon', 'Bob');
-- Succeeds: different time_slot

INSERT INTO room_bookings (room_number, booking_date, time_slot, booked_by)
VALUES (101, '2025-04-15', 'morning', 'Charlie');
-- ERROR: duplicate key value violates unique constraint

Named UNIQUE Constraints

Naming constraints improves error messages and maintainability:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  CONSTRAINT uq_users_email UNIQUE (email)
);

Adding UNIQUE to an Existing Table

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

If existing data violates the constraint, the ALTER TABLE command will fail. You must clean up duplicates first.

Removing a UNIQUE Constraint

ALTER TABLE users DROP CONSTRAINT uq_users_email;

CHECK Constraints

A CHECK constraint validates that a column value satisfies a Boolean expression. Any insert or update that would violate the check is rejected.

Basic CHECK Constraints

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC NOT NULL CHECK (price > 0),
  quantity INTEGER NOT NULL CHECK (quantity >= 0),
  discount NUMERIC CHECK (discount >= 0 AND discount <= 1)
);

Attempting to insert invalid data:

INSERT INTO products (name, price, quantity) VALUES ('Widget', -5.00, 10);
-- ERROR: new row for relation "products" violates check constraint "products_price_check"
-- DETAIL: Failing row contains (1, Widget, -5.00, 10, null).

Named CHECK Constraints

Always name your CHECK constraints for clarity:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC NOT NULL,
  quantity INTEGER NOT NULL,
  discount NUMERIC,
  CONSTRAINT chk_price_positive CHECK (price > 0),
  CONSTRAINT chk_quantity_non_negative CHECK (quantity >= 0),
  CONSTRAINT chk_discount_range CHECK (discount >= 0 AND discount <= 1)
);

Multi-Column CHECK Constraints

CHECK constraints can reference multiple columns in the same row:

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  CONSTRAINT chk_date_order CHECK (end_date >= start_date)
);

INSERT INTO events (name, start_date, end_date) VALUES ('Conference', '2025-06-15', '2025-06-10');
-- ERROR: new row for relation "events" violates check constraint "chk_date_order"

Another example -- a shipping table where the shipped date must be on or after the order date:

CREATE TABLE shipments (
  id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  ship_date DATE,
  delivery_date DATE,
  CONSTRAINT chk_ship_after_order CHECK (ship_date IS NULL OR ship_date >= order_date),
  CONSTRAINT chk_deliver_after_ship CHECK (delivery_date IS NULL OR delivery_date >= ship_date)
);

Common CHECK Patterns

Restricting to a set of values (like an enum):

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  status TEXT NOT NULL,
  CONSTRAINT chk_valid_status CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);

Validating string length:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3 AND LENGTH(username) <= 30)
);

Validating email format (basic):

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  CONSTRAINT chk_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

Percentage fields:

CREATE TABLE tax_rates (
  id SERIAL PRIMARY KEY,
  region TEXT NOT NULL,
  rate NUMERIC NOT NULL,
  CONSTRAINT chk_rate_percentage CHECK (rate >= 0 AND rate <= 100)
);

Adding CHECK to an Existing Table

ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0);

If existing data violates the constraint, the command fails. You can add the constraint without validating existing data using NOT VALID:

ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0) NOT VALID;

This allows existing rows to violate the constraint but enforces it for all future inserts and updates. You can later validate existing data:

ALTER TABLE products VALIDATE CONSTRAINT chk_price_positive;

Removing a CHECK Constraint

ALTER TABLE products DROP CONSTRAINT chk_price_positive;

Combining UNIQUE and CHECK

You can use both constraints together for comprehensive validation:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  employee_code TEXT NOT NULL,
  email TEXT NOT NULL,
  salary NUMERIC NOT NULL,
  department TEXT NOT NULL,
  CONSTRAINT uq_employee_code UNIQUE (employee_code),
  CONSTRAINT uq_employee_email UNIQUE (email),
  CONSTRAINT chk_salary_range CHECK (salary >= 30000 AND salary <= 500000),
  CONSTRAINT chk_department_valid CHECK (department IN ('Engineering', 'Sales', 'Marketing', 'HR', 'Finance'))
);

Limitations of CHECK Constraints

1. Row-level only -- CHECK constraints can only reference columns within the current row. They cannot reference other rows in the same table or rows in other tables.

2. No subqueries -- You cannot use SELECT statements inside a CHECK constraint.

3. Evaluated on insert and update only -- CHECK constraints are not re-evaluated when other tables change. For cross-table validation, use triggers.

Try It Yourself

Think through how you would design constraints for these scenarios:

1. A courses table where each course has a unique course_code and the credits value must be between 1 and 6.

2. A reservations table where the same customer cannot book the same room on the same date (use multi-column UNIQUE), and the check_out date must be after the check_in date.

3. A products table where the sale_price must always be less than or equal to the regular_price, and both must be positive.