Constraints & Data Integrity

What Are Constraints?

Constraints are rules enforced at the database level that prevent invalid data from entering your tables. They are the first line of defence for data quality.

Why constraints matter:

• Invalid data caught at insertion/update time, not later in application bugs
• Rules are enforced even when multiple applications share the same database
• Self-documenting schema — constraints communicate business rules clearly

-- Without constraints: any garbage gets through
INSERT INTO employees VALUES (NULL, NULL, -50000, 'not-a-date');  -- accepted!

-- With constraints: database rejects it
-- NOT NULL, CHECK, and type enforcement stop this at the gate

PRIMARY KEY

Every table should have a primary key — a column (or combination) that uniquely identifies each row. It automatically implies NOT NULL and UNIQUE.

Single Column Primary Key

-- Integer with auto-increment (SQLite / MySQL)
CREATE TABLE employees (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    name   TEXT    NOT NULL
);

-- PostgreSQL: use SERIAL or GENERATED
CREATE TABLE employees (
    id     SERIAL  PRIMARY KEY,   -- auto-incrementing integer
    name   TEXT    NOT NULL
);
-- Or more explicitly:
CREATE TABLE employees (
    id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name   TEXT NOT NULL
);

Composite Primary Key

When a single column isn't enough to uniquely identify a row, use a composite key:

CREATE TABLE order_items (
    order_id    INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL DEFAULT 1,

    PRIMARY KEY (order_id, product_id)  -- table-level constraint
);

No two rows can have the same (order_id, product_id) combination.

Natural vs Surrogate Keys

Type	Description	Example
Natural key	Meaningful business value	Email, PAN number, ISBN
Surrogate key	System-generated identifier	Auto-increment ID, UUID

Prefer surrogate keys (auto-increment) for most tables — natural keys change (emails change, names change) and can cause cascading updates.

NOT NULL

Prevents a column from storing NULL — the value is required:

CREATE TABLE employees (
    id         INTEGER  PRIMARY KEY,
    name       TEXT     NOT NULL,    -- required
    email      TEXT,                 -- optional (allows NULL)
    department TEXT     NOT NULL,    -- required
    salary     REAL     NOT NULL
);

-- This fails because name is NOT NULL
INSERT INTO employees (id, email, department, salary)
VALUES (1, 'a@b.com', 'Finance', 75000);
-- Error: NOT NULL constraint failed: employees.name

When to Use NOT NULL

Use NOT NULL for columns that must always have a value:

• Identifiers, foreign keys, timestamps, amounts
• Anything where NULL would make the row meaningless

Allow NULL for truly optional data: middle name, phone number, notes.

UNIQUE

Ensures no two rows have the same value in the column (or combination of columns). Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns may contain NULL (NULLs are considered distinct from each other in most databases):

CREATE TABLE employees (
    id     INTEGER PRIMARY KEY,
    name   TEXT    NOT NULL,
    email  TEXT    UNIQUE,     -- no two employees share email
    phone  TEXT    UNIQUE      -- no two employees share phone
);

-- Composite unique: combination must be unique
CREATE TABLE registrations (
    event_id   INTEGER NOT NULL,
    user_id    INTEGER NOT NULL,
    UNIQUE (event_id, user_id)    -- same user can't register twice for same event
);

NULL in UNIQUE Columns

-- Most databases allow multiple NULL values in a UNIQUE column
-- (because NULL != NULL)
INSERT INTO employees (id, name, email) VALUES (1, 'Priya', NULL);
INSERT INTO employees (id, name, email) VALUES (2, 'Raj',   NULL);  -- allowed!

DEFAULT

Provides a fallback value when none is given in an INSERT:

CREATE TABLE employees (
    id          INTEGER  PRIMARY KEY AUTOINCREMENT,
    name        TEXT     NOT NULL,
    salary      REAL     NOT NULL    DEFAULT 50000,
    is_active   INTEGER  DEFAULT 1,
    joined_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    tier        TEXT      DEFAULT 'Junior'
);

-- salary, is_active, joined_at, tier all get defaults
INSERT INTO employees (name) VALUES ('Dev Kumar');

Dynamic Defaults

DEFAULT CURRENT_TIMESTAMP   -- current date and time
DEFAULT CURRENT_DATE        -- current date only
DEFAULT (RANDOM())          -- PostgreSQL: random number

CHECK

Validates data against an expression. The row is rejected if the expression evaluates to FALSE:

CREATE TABLE employees (
    id         INTEGER  PRIMARY KEY,
    name       TEXT     NOT NULL,
    salary     REAL     NOT NULL CHECK (salary >= 0),
    hire_date  TEXT     NOT NULL,
    age        INTEGER  CHECK (age >= 18 AND age <= 65),
    rating     DECIMAL  CHECK (rating BETWEEN 1.0 AND 5.0)
);

CREATE TABLE orders (
    id         INTEGER  PRIMARY KEY,
    status     TEXT     NOT NULL
        CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    quantity   INTEGER  CHECK (quantity > 0),
    unit_price DECIMAL  CHECK (unit_price >= 0)
);

Multi-Column CHECK

CREATE TABLE projects (
    id         INTEGER PRIMARY KEY,
    start_date DATE    NOT NULL,
    end_date   DATE,
    budget     DECIMAL,

    CHECK (end_date IS NULL OR end_date > start_date),
    CHECK (budget IS NULL OR budget >= 0)
);

Named CHECK Constraints

Naming constraints makes error messages clearer and allows you to drop a specific constraint later:

CREATE TABLE employees (
    salary REAL NOT NULL,
    CONSTRAINT chk_positive_salary CHECK (salary >= 0),
    CONSTRAINT chk_reasonable_salary CHECK (salary < 10000000)
);

-- Drop a named constraint later
ALTER TABLE employees DROP CONSTRAINT chk_reasonable_salary;

FOREIGN KEY

A foreign key enforces referential integrity — it ensures a value in one table exists in another table's primary key.

CREATE TABLE departments (
    id   INTEGER PRIMARY KEY,
    name TEXT    NOT NULL
);

CREATE TABLE employees (
    id            INTEGER PRIMARY KEY,
    name          TEXT    NOT NULL,
    department_id INTEGER NOT NULL,

    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- This fails if department_id = 99 doesn't exist in departments
INSERT INTO employees (id, name, department_id) VALUES (1, 'Priya', 99);
-- Error: FOREIGN KEY constraint failed

Referential Actions

Control what happens when the parent row is deleted or updated:

CREATE TABLE employees (
    id            INTEGER PRIMARY KEY,
    department_id INTEGER,

    FOREIGN KEY (department_id) REFERENCES departments(id)
        ON DELETE SET NULL   -- employee stays, dept becomes NULL
        ON UPDATE CASCADE    -- if dept.id changes, update here too
);

-- OR
FOREIGN KEY (department_id) REFERENCES departments(id)
    ON DELETE CASCADE        -- deleting dept deletes all its employees
    ON DELETE RESTRICT       -- reject dept deletion if employees exist (default)

Action	ON DELETE	ON UPDATE
RESTRICT	Block parent delete if children exist	Block parent update if children exist
CASCADE	Delete children automatically	Update FK in children
SET NULL	Set FK to NULL in children	Set FK to NULL
SET DEFAULT	Set FK to default value	Set FK to default value
NO ACTION	Like RESTRICT but deferred	Like RESTRICT but deferred

Self-Referencing Foreign Key

-- Manager-employee: both in same table
CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT    NOT NULL,
    manager_id INTEGER,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

Enabling FK Enforcement in SQLite

SQLite has foreign key support but it's off by default:

PRAGMA foreign_keys = ON;  -- Run this at the start of every connection

Constraint Summary

Constraint	Prevents	Notes
PRIMARY KEY	Duplicates + NULLs in the key	One per table
UNIQUE	Duplicate values	Multiple allowed; NULLs typically exempt
NOT NULL	Empty values	Column always requires a value
DEFAULT	Missing values	Provides fallback
CHECK	Values violating a rule	Expression must evaluate to TRUE or NULL
FOREIGN KEY	Orphan references	Enforces relational integrity

Deferred Constraints (PostgreSQL)

Normally constraints are checked immediately after each statement. With DEFERRABLE, you can defer checking until the end of the transaction:

ALTER TABLE employees
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id) REFERENCES employees(id)
DEFERRABLE INITIALLY DEFERRED;

Useful when inserting rows that reference each other (chicken-and-egg problems).

Practical Example: Complete Order System Schema

CREATE TABLE customers (
    id         INTEGER      PRIMARY KEY AUTOINCREMENT,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(150) NOT NULL UNIQUE,
    tier       VARCHAR(20)  DEFAULT 'Standard'
        CHECK (tier IN ('Standard', 'Premium', 'VIP')),
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id          INTEGER       PRIMARY KEY AUTOINCREMENT,
    sku         VARCHAR(30)   NOT NULL UNIQUE,
    name        VARCHAR(200)  NOT NULL,
    price       DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock_qty   INTEGER       NOT NULL DEFAULT 0 CHECK (stock_qty >= 0)
);

CREATE TABLE orders (
    id          INTEGER       PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER       NOT NULL,
    order_date  DATE          NOT NULL DEFAULT CURRENT_DATE,
    status      VARCHAR(20)   NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending','confirmed','shipped','delivered','cancelled')),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

CREATE TABLE order_items (
    order_id   INTEGER       NOT NULL,
    product_id INTEGER       NOT NULL,
    quantity   INTEGER       NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),

    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

Common Mistakes

1. Missing FK enforcement in SQLite

-- SQLite silently ignores FK violations without this pragma
PRAGMA foreign_keys = ON;

2. CHECK constraints don't validate NULL

-- A NULL salary passes the CHECK constraint
salary REAL CHECK (salary >= 0)

-- To also block NULLs:
salary REAL NOT NULL CHECK (salary >= 0)

3. Dropping parent before child

-- Error if employees.department_id references departments.id
DROP TABLE departments;  -- FK constraint violation

-- Drop child first
DROP TABLE employees;
DROP TABLE departments;

Practice Exercises

1. Create a students table with constraints: id (PK auto-increment), name (NOT NULL), email (UNIQUE NOT NULL), gpa (CHECK 0.0 to 4.0), status (DEFAULT 'active', CHECK IN ('active','inactive','graduated')).
2. Add a courses table and a enrollments table with a composite PK on (student_id, course_id) and FK constraints.
3. Add a named CHECK constraint to the employees table ensuring salary is below 5,000,000.
4. What happens if you insert an employee with a non-existent department_id (assuming FK is enforced)?
5. How would you allow NULL in a UNIQUE column but still prevent duplicate non-NULL values?

Summary

In this chapter you learned:

• PRIMARY KEY — unique identifier per row; implies NOT NULL + UNIQUE
• Surrogate keys (auto-increment) are preferred over natural keys
• NOT NULL — column must always have a value
• UNIQUE — no two rows share a value; NULLs are usually allowed
• DEFAULT — fallback value when INSERT doesn't provide one
• CHECK (expr) — validates values; NULL passes by default (combine with NOT NULL)
• FOREIGN KEY — ensures referenced values exist; control cascades with ON DELETE/UPDATE
• Name your constraints to make error messages clear and allow targeted removal
• SQLite: run PRAGMA foreign_keys = ON to enable FK enforcement

Next up: Indexes & Query Performance — speed up queries on large datasets.