Constraints

Constraints are rules enforced by the database on the data stored in a table. They are your first line of defence against bad data — ensuring that invalid, incomplete, or inconsistent values never make it into the database in the first place.

Why Constraints Matter

Imagine a bank database that allows:

• Two accounts with the same account number
• A transaction referencing a customer who does not exist
• A negative balance stored as an account balance
• An employee with no name

Any of these would cause serious problems — wrong reports, failed lookups, confused application code. Constraints prevent all of these at the database level, so bugs in application code cannot silently corrupt data.

Think of constraints like guardrails on a road. The car (your application) drives on the road (the database), and the guardrails (constraints) stop it from going over the edge even if the driver makes a mistake.

All Constraints in One Example

CREATE TABLE employees (
    emp_id     INT              PRIMARY KEY,               -- unique + NOT NULL
    name       VARCHAR(100)     NOT NULL,                  -- cannot be empty
    email      VARCHAR(200)     UNIQUE,                    -- no duplicate emails
    dept_id    INT              REFERENCES departments(dept_id),  -- foreign key
    salary     DECIMAL(10,2)    CHECK (salary > 0),        -- must be positive
    status     VARCHAR(20)      DEFAULT 'active'            -- auto-filled on INSERT
);

Let us now examine each constraint in depth.

PRIMARY KEY

The primary key is the unique identifier for each row. Every table should have one.

Rules:

• Values must be unique — no two rows can share the same primary key
• Values cannot be NULL — every row must have an identifier
• There can be only one primary key per table (though it can span multiple columns)
• The database automatically creates a unique index on the primary key column(s)

-- Column-level PRIMARY KEY (most common)
CREATE TABLE products (
    product_id   INT          PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL
);

-- Table-level PRIMARY KEY (identical effect for single columns)
CREATE TABLE products (
    product_id   INT,
    product_name VARCHAR(200) NOT NULL,
    PRIMARY KEY (product_id)
);

-- Named constraint — recommended for clarity and easy management
CREATE TABLE products (
    product_id   INT,
    product_name VARCHAR(200) NOT NULL,
    CONSTRAINT pk_products PRIMARY KEY (product_id)
);

Composite Primary Key — Multiple Columns Together

Sometimes no single column can uniquely identify a row. In that case, a combination of columns forms the primary key.

-- An order_item is uniquely identified by its order AND its product together
-- (the same product can appear in different orders, and each order has many products)
CREATE TABLE order_items (
    order_id    INT     NOT NULL,
    product_id  INT     NOT NULL,
    quantity    INT     DEFAULT 1,
    unit_price  DECIMAL(10,2),
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

With this composite primary key, you can have (order_id=101, product_id=5) and (order_id=102, product_id=5) — both are valid because the combination is unique.

When to use composite primary keys:

• Junction/bridge tables (many-to-many relationships)
• When the business identifies records by a combination (e.g., country_code + zip_code)

FOREIGN KEY

A foreign key creates a link between two tables. It says: "the value in this column must exist as a primary key in that other table." This is called referential integrity.

CREATE TABLE departments (
    dept_id   INT          PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    emp_id   INT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    dept_id  INT,
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
);

With this constraint in place:

• You cannot insert an employee with dept_id = 99 if there is no department with dept_id = 99
• The database prevents you from accidentally orphaning employees by deleting departments

ON DELETE and ON UPDATE Actions

What should happen to child rows when the referenced parent row is deleted or updated?

CREATE TABLE employees (
    emp_id  INT PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    dept_id INT,
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON DELETE SET NULL    -- if dept deleted, set employee's dept_id to NULL
        ON UPDATE CASCADE     -- if dept_id value changes, update employees too
);

Common scenarios:

-- Soft parent delete: preserve children but mark them as unassigned
ON DELETE SET NULL   -- employee.dept_id becomes NULL when dept is deleted

-- Hard cascade: delete all orders when a customer is deleted
ON DELETE CASCADE    -- order rows are deleted when the customer row is deleted

-- Strict protection: prevent deleting a department that has employees
ON DELETE RESTRICT   -- (the default in most databases)

UNIQUE Constraint

The UNIQUE constraint ensures no two rows have the same value in the constrained column(s). Unlike PRIMARY KEY, UNIQUE allows NULL values (and in most databases, allows multiple NULL values, since NULL ≠ NULL).

CREATE TABLE users (
    user_id  INT          PRIMARY KEY,
    email    VARCHAR(200) UNIQUE,      -- no two users can have the same email
    username VARCHAR(50)  UNIQUE,      -- no two users can have the same username
    phone    VARCHAR(20)               -- no uniqueness constraint
);

A table can have multiple UNIQUE constraints (unlike PRIMARY KEY which allows only one).

Composite UNIQUE Constraint

Just like composite primary keys, UNIQUE can span multiple columns — the combination must be unique:

-- A room can be booked many times, but not twice on the same day
CREATE TABLE room_bookings (
    booking_id  INT  PRIMARY KEY,
    room_id     INT  NOT NULL,
    booking_date DATE NOT NULL,
    CONSTRAINT uq_room_date UNIQUE (room_id, booking_date)
);

UNIQUE vs PRIMARY KEY

NOT NULL Constraint

NOT NULL means the column must always have a value — an INSERT or UPDATE that leaves the column empty is rejected.

CREATE TABLE employees (
    emp_id  INT          PRIMARY KEY,    -- PRIMARY KEY implies NOT NULL
    name    VARCHAR(100) NOT NULL,       -- explicit NOT NULL
    email   VARCHAR(200),                -- NULLs allowed (column is optional)
    salary  DECIMAL(10,2) NOT NULL       -- salary is required
);

-- This will fail — name cannot be NULL
INSERT INTO employees (emp_id, name, salary) VALUES (1, NULL, 50000);
-- ERROR: null value in column "name" violates not-null constraint

CHECK Constraint

CHECK defines a custom rule that every value in the column must satisfy. Think of it as a validation rule baked directly into the database schema.

CREATE TABLE products (
    product_id  INT         PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    price       DECIMAL(8,2) CHECK (price >= 0),          -- price can't be negative
    stock       INT          CHECK (stock >= 0),           -- stock can't be negative
    rating      INT          CHECK (rating BETWEEN 1 AND 5),  -- rating 1-5 only
    category    VARCHAR(50)  CHECK (category IN ('Electronics', 'Clothing', 'Food'))
);

Table-Level CHECK — Multiple Columns

Sometimes a validation rule involves more than one column. Put it at the table level:

CREATE TABLE employees (
    emp_id     INT  PRIMARY KEY,
    hire_date  DATE NOT NULL,
    end_date   DATE,
    salary     DECIMAL(10,2) CHECK (salary > 0),
    bonus      DECIMAL(10,2),
    -- Cross-column rule: end_date must be after hire_date (if it exists)
    CONSTRAINT chk_dates CHECK (end_date IS NULL OR end_date > hire_date),
    -- Cross-column rule: bonus cannot exceed salary
    CONSTRAINT chk_bonus CHECK (bonus IS NULL OR bonus <= salary)
);

-- This will fail:
INSERT INTO employees (emp_id, hire_date, end_date, salary)
VALUES (1, '2024-01-01', '2023-01-01', 50000);
-- ERROR: check constraint "chk_dates" violated (end_date before hire_date)

DEFAULT Constraint

DEFAULT specifies a value to use when INSERT does not provide one for that column.

CREATE TABLE orders (
    order_id    INT         PRIMARY KEY,
    order_date  DATE        DEFAULT CURRENT_DATE,   -- today's date
    status      VARCHAR(20) DEFAULT 'pending',
    quantity    INT         DEFAULT 1,
    priority    INT         DEFAULT 3               -- medium priority
);

-- Only provide order_id — all other columns use their defaults
INSERT INTO orders (order_id) VALUES (1001);
-- Result: order_date=today, status='pending', quantity=1, priority=3

-- Provide some values, let defaults fill the rest
INSERT INTO orders (order_id, quantity) VALUES (1002, 5);
-- Result: order_date=today, status='pending', quantity=5, priority=3

-- Explicitly override a default
INSERT INTO orders (order_id, status) VALUES (1003, 'express');
-- Result: status='express' (overrides the default)

Common DEFAULT values:

created_at  TIMESTAMP  DEFAULT CURRENT_TIMESTAMP   -- record creation time
is_active   BOOLEAN    DEFAULT TRUE
currency    CHAR(3)    DEFAULT 'USD'
version     INT        DEFAULT 1

Column-Level vs Table-Level Constraints

Constraints can be defined in two places:

Column level — attached directly to a column definition (cleaner for single-column constraints):

CREATE TABLE employees (
    emp_id   INT          PRIMARY KEY,            -- column-level PK
    email    VARCHAR(200) UNIQUE,                 -- column-level UNIQUE
    salary   DECIMAL      CHECK (salary > 0),     -- column-level CHECK
    dept_id  INT          REFERENCES departments  -- column-level FK
);

Table level — defined separately after all columns (required for multi-column constraints):

CREATE TABLE order_items (
    order_id    INT         NOT NULL,
    product_id  INT         NOT NULL,
    quantity    INT         DEFAULT 1,
    price       DECIMAL(10,2) NOT NULL,
    -- Table-level constraints:
    CONSTRAINT pk_order_items    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_oi_order       FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT fk_oi_product     FOREIGN KEY (product_id) REFERENCES products(product_id),
    CONSTRAINT chk_quantity      CHECK (quantity > 0)
);

Adding and Dropping Constraints with ALTER TABLE

Constraints can be added or removed from existing tables without recreating them.

Adding Constraints

-- Add a NOT NULL constraint (Oracle syntax)
ALTER TABLE employees MODIFY salary NOT NULL;

-- Add a NOT NULL constraint (PostgreSQL / SQL Server syntax)
ALTER TABLE employees ALTER COLUMN salary SET NOT NULL;

-- Add a CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Add a UNIQUE constraint
ALTER TABLE employees
ADD CONSTRAINT uq_email UNIQUE (email);

-- Add a FOREIGN KEY constraint
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE SET NULL;

-- Add a DEFAULT value (Oracle)
ALTER TABLE orders MODIFY status DEFAULT 'pending';

-- Add a DEFAULT value (PostgreSQL / SQL Server)
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';

Dropping Constraints

-- Drop a named constraint (works in Oracle, PostgreSQL, MySQL, SQL Server)
ALTER TABLE employees DROP CONSTRAINT chk_salary;

-- Drop a primary key
ALTER TABLE employees DROP CONSTRAINT pk_employees;

-- Drop a NOT NULL constraint (Oracle)
ALTER TABLE employees MODIFY salary NULL;

-- Drop a NOT NULL constraint (PostgreSQL)
ALTER TABLE employees ALTER COLUMN salary DROP NOT NULL;

-- Drop a DEFAULT (Oracle)
ALTER TABLE orders MODIFY status DEFAULT NULL;

-- Drop a DEFAULT (PostgreSQL)
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;

Viewing Existing Constraints

-- Oracle: view all constraints on a table
SELECT constraint_name,
       constraint_type,
       column_name,
       status
FROM   user_cons_columns
WHERE  table_name = 'EMPLOYEES'
ORDER BY constraint_type, constraint_name;

-- Oracle: constraint type codes: P=Primary, R=Foreign (Referential), U=Unique, C=Check/NotNull
SELECT constraint_name, constraint_type, search_condition
FROM   user_constraints
WHERE  table_name = 'EMPLOYEES';

-- PostgreSQL: view constraints
SELECT conname, contype, pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  conrelid = 'employees'::regclass;

-- MySQL: view constraints
SELECT constraint_name, constraint_type
FROM   information_schema.table_constraints
WHERE  table_name = 'employees';

What Happens When You Violate a Constraint

When an INSERT, UPDATE, or DELETE violates a constraint, the database raises an error and rolls back the offending statement. No data is changed.

-- PRIMARY KEY violation (duplicate emp_id)
INSERT INTO employees (emp_id, name) VALUES (1, 'Alice');
INSERT INTO employees (emp_id, name) VALUES (1, 'Bob');
-- ERROR: duplicate key value violates unique constraint "pk_employees"
-- ORA-00001: unique constraint (HR.PK_EMPLOYEES) violated

-- NOT NULL violation
INSERT INTO employees (emp_id) VALUES (2);
-- ERROR: null value in column "name" violates not-null constraint

-- FOREIGN KEY violation (department 99 does not exist)
INSERT INTO employees (emp_id, name, dept_id) VALUES (3, 'Carol', 99);
-- ERROR: insert or update on table "employees" violates foreign key constraint
-- ORA-02291: integrity constraint violated - parent key not found

-- CHECK violation (negative salary)
INSERT INTO employees (emp_id, name, salary) VALUES (4, 'Dave', -5000);
-- ERROR: new row violates check constraint "chk_salary"

-- UNIQUE violation (duplicate email)
INSERT INTO users (user_id, email) VALUES (1, 'a@test.com');
INSERT INTO users (user_id, email) VALUES (2, 'a@test.com');
-- ERROR: duplicate key value violates unique constraint "uq_email"

Constraint Naming Best Practices

Good constraint names make error messages useful and make schema changes easier:

-- Good: meaningful constraint names
CONSTRAINT pk_orders               PRIMARY KEY (order_id)
CONSTRAINT fk_orders_customers     FOREIGN KEY (customer_id) REFERENCES customers
CONSTRAINT fk_orders_products      FOREIGN KEY (product_id) REFERENCES products
CONSTRAINT uq_orders_reference     UNIQUE (reference_number)
CONSTRAINT chk_orders_total        CHECK (total_amount >= 0)

-- Bad: auto-generated names (what the database creates if you don't name them)
-- SYS_C007234, SYS_C007235 ... meaningless in error messages

Common Errors

Interview Corner

CREATE TABLE employees (
    employee_id  NUMBER PRIMARY KEY,          -- one PK
    email        VARCHAR2(100) UNIQUE,        -- unique, NULLs allowed
    badge_number VARCHAR2(20)  UNIQUE         -- second unique constraint
);

ALTER TABLE employees DISABLE CONSTRAINT fk_emp_dept;
-- perform bulk load…
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT fk_emp_dept;
-- NOVALIDATE: enforce going forward, but don't check existing rows

Related Topics

• DDL Commands — CREATE TABLE, ALTER TABLE, DROP — the commands that define and modify constraints
• DML Commands — INSERT, UPDATE, DELETE — operations that trigger constraint checks
• Indexes — PRIMARY KEY and UNIQUE constraints automatically create unique indexes
• Data Dictionary — USER_CONSTRAINTS and USER_CONS_COLUMNS to inspect constraint definitions