Constraints

A constraint is a rule enforced by the database on the data in a table.

Concept

A constraint is a rule enforced by the database on the data in a table. Constraints guarantee data integrity no matter which application, script, or user modifies the data.

Entity integrity (every row identifiable) Referential integrity (relationships are valid) Domain integrity (values are valid) Data type, NOT NULL, CHECK, DEFAULT

Uniqueness (no duplicates of a business key)

Why It Matters

Application validation can be bypassed (bugs, scripts, direct DB access, multiple apps). Database constraints cannot.

Constraints document business rules in the schema.

Optimizers can use constraints (e.g., a UNIQUE or NOT NULL constraint can simplify plans).

Syntax

-- Column-level (inline, applies to one column)
column_name data_type [CONSTRAINT name] constraint_type

-- Table-level (after columns, can cover multiple columns)
[CONSTRAINT name] PRIMARY KEY (col1, col2)
[CONSTRAINT name] UNIQUE (col1, col2)
[CONSTRAINT name] FOREIGN KEY (col) REFERENCES parent(col)
    [ON DELETE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}]
    [ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}]
[CONSTRAINT name] CHECK (condition)

Example

5.1 PRIMARY KEY Uniquely identifies each row. Implies UNIQUE + NOT NULL. Only one per table. Most databases automatically create an index on it (in SQL Server and MySQL InnoDB, a clustered index by default).

Attempting a duplicate:

5.2 FOREIGN KEY A foreign key column in a child table must contain a value that exists in the parent table's primary key (or unique key), or be NULL (if allowed).

Referential integrity rules:

You cannot insert a child row whose FK value does not exist in the parent.

You cannot update a child FK to a non-existent parent value.

You cannot delete/update a parent row that has children, unless a referential action (CASCADE, SET NULL, ...) says what to do.

Note: MySQL enforces foreign keys only with the InnoDB engine (default since 5.5). SQLite enforces them only after PRAGMA foreign_keys = ON; on each connection.

5.3 Referential Actions: ON DELETE / ON UPDATE On parent delete/update NO ACTION (default)

Error if children exist (checked at end of statement; deferrable in PostgreSQL/Oracle) Error immediately if children exist Delete/update the child rows too Composition (order → order_items) Set child FK to NULL

Optional relationship (employee → department) Set child FK to its default value Rare (not supported by MySQL InnoDB)

Oracle supports ON DELETE CASCADE and ON DELETE SET NULL only; it has no ON UPDATE clause (primary keys are expected not to change).

Walkthrough of ON DELETE CASCADE:

Walkthrough of ON DELETE SET NULL:

Use CASCADE carefully: a single DELETE can silently remove thousands of rows across many tables.

Ensures no two rows have the same value (or combination of values).

PRIMARY KEY vs UNIQUE:

Many per table NULL allowed (see below) Row identifier; target of FKs

Alternate key; can also be target of FKs Often the clustered index Usually a non-clustered index NULL behavior of UNIQUE differs:

PostgreSQL, MySQL, Oracle, SQLite: multiple NULLs allowed (NULL is not equal to NULL). PostgreSQL 15+ can use UNIQUE NULLS NOT DISTINCT to allow only one.

SQL Server: only one NULL allowed in a UNIQUE constraint. Use a filtered unique index CREATE UNIQUE INDEX ... WHERE col IS NOT NULL to allow many.

A CHECK constraint enforces a boolean condition. A row is rejected if the condition is FALSE. A row is accepted if the condition is TRUE or UNKNOWN (NULL).

Notes: MySQL ignored CHECK constraints before 8.0.16 (parsed but not enforced). CHECK constraints normally cannot reference other tables or use subqueries; use foreign keys or triggers for that.

An explicit NULL in an INSERT does not trigger the default:

5.8 AUTO_INCREMENT / IDENTITY / SERIAL Automatically generated surrogate keys (detailed in Module 29):

5.9 Column-Level vs Table-Level vs Composite Constraints Next to the column definition After all columns Only column-level (standard) Composite PK/UNIQUE/FK CHECK comparing columns

Possible in some DBMS, cleaner at table level A composite foreign key must reference a composite key of the same column count and types:

5.10 Viewing, Disabling, and Dropping Constraints

-- Column-level
CREATE TABLE brands (
    brand_id  INT PRIMARY KEY,
    name      VARCHAR(50) NOT NULL
);

-- Table-level, named
CREATE TABLE models (
    model_id  INT,
    brand_id  INT NOT NULL,
    name      VARCHAR(50) NOT NULL,
    CONSTRAINT pk_models PRIMARY KEY (model_id)
);

-- Composite primary key (must be table-level)
CREATE TABLE enrollments (
    student_id  INT,
    course_id   INT,
    enrolled_on DATE NOT NULL,
    CONSTRAINT pk_enrollments PRIMARY KEY (student_id, course_id)
);

INSERT INTO brands VALUES (1, 'Tata');
INSERT INTO brands VALUES (1, 'Mahindra');
-- ERROR: duplicate key value violates unique constraint "brands_pkey"   (PostgreSQL)
-- ERROR 1062: Duplicate entry '1' for key 'PRIMARY'                    (MySQL)

CREATE TABLE models (
    model_id  INT PRIMARY KEY,
    brand_id  INT NOT NULL,
    name      VARCHAR(50) NOT NULL,
    CONSTRAINT fk_models_brands
        FOREIGN KEY (brand_id) REFERENCES brands(brand_id)
);

INSERT INTO models VALUES (10, 99, 'Unknown');
-- ERROR: insert or update on table "models" violates foreign key constraint "fk_models_brands"
-- DETAIL: Key (brand_id)=(99) is not present in table "brands".

CREATE TABLE orders_demo (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL,
    CONSTRAINT fk_orders_demo_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT            -- cannot delete a customer who has orders
        ON UPDATE CASCADE             -- if customer_id changes, follow it
);

CREATE TABLE order_lines_demo (
    order_id    INT NOT NULL,
    line_no     INT NOT NULL,
    product_id  INT NOT NULL,
    PRIMARY KEY (order_id, line_no),
    CONSTRAINT fk_lines_order
        FOREIGN KEY (order_id) REFERENCES orders_demo(order_id)
        ON DELETE CASCADE             -- deleting an order deletes its lines
);

CREATE TABLE staff_demo (
    staff_id  INT PRIMARY KEY,
    dept_id   INT NULL,
    CONSTRAINT fk_staff_dept
        FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL            -- deleting a department leaves staff unassigned
);

orders_demo                 order_lines_demo
+----------+                +----------+---------+
| order_id |                | order_id | line_no |
+----------+                +----------+---------+
| 500      |  <── cascade ──| 500      | 1       |
+----------+                | 500      | 2       |
                            +----------+---------+
DELETE FROM orders_demo WHERE order_id = 500;
→ order 500 is deleted AND lines (500,1), (500,2) are deleted automatically.

DELETE FROM departments WHERE dept_id = 3;
→ every staff_demo row with dept_id = 3 now has dept_id = NULL.

CREATE TABLE app_users (
    user_id   INT PRIMARY KEY,
    username  VARCHAR(30)  NOT NULL UNIQUE,
    email     VARCHAR(100) NOT NULL,
    CONSTRAINT uq_app_users_email UNIQUE (email)
);

-- Composite unique: one seat number per screening
CREATE TABLE bookings (
    booking_id    INT PRIMARY KEY,
    screening_id  INT NOT NULL,
    seat_no       VARCHAR(5) NOT NULL,
    CONSTRAINT uq_bookings_seat UNIQUE (screening_id, seat_no)
);

CREATE TABLE patients (
    patient_id  INT PRIMARY KEY,
    full_name   VARCHAR(100) NOT NULL,
    phone       VARCHAR(15)  NULL,      -- optional (NULL is the default if omitted)
    blood_group VARCHAR(3)   NOT NULL
);

INSERT INTO patients (patient_id, blood_group) VALUES (1, 'O+');
-- ERROR: null value in column "full_name" violates not-null constraint

CREATE TABLE accounts (
    account_id    INT PRIMARY KEY,
    account_type  VARCHAR(10) NOT NULL CHECK (account_type IN ('SAVINGS', 'CURRENT', 'FD')),
    balance       DECIMAL(15,2) NOT NULL CHECK (balance >= 0),
    opened_on     DATE NOT NULL,
    closed_on     DATE,
    CONSTRAINT chk_accounts_dates CHECK (closed_on IS NULL OR closed_on >= opened_on)
);

INSERT INTO accounts VALUES (1, 'SAVINGS', -500, DATE '2026-01-01', NULL);
-- ERROR: new row violates check constraint "accounts_balance_check"

CREATE TABLE support_tickets (
    ticket_id   INT PRIMARY KEY,
    status      VARCHAR(20) NOT NULL DEFAULT 'OPEN',
    priority    SMALLINT    NOT NULL DEFAULT 3,
    created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO support_tickets (ticket_id) VALUES (1);
INSERT INTO support_tickets (ticket_id, status) VALUES (2, DEFAULT);   -- explicit DEFAULT keyword

Expected Result

INSERT INTO accounts VALUES (1, 'SAVINGS', 10000, DATE '2026-01-01', NULL);      -- OK
INSERT INTO accounts VALUES (2, 'LOAN',    10000, DATE '2026-01-01', NULL);      -- fails: account_type CHECK
INSERT INTO accounts VALUES (3, 'CURRENT', 5000,  DATE '2026-02-01', DATE '2026-01-01'); -- fails: chk_accounts_dates
SELECT account_id, account_type, balance FROM accounts;

Common Mistakes

Relying only on application validation and skipping database constraints.

Forgetting that CHECK (x > 0) accepts NULL; add NOT NULL if the value is required.

Using ON DELETE CASCADE on relationships where deleting a parent should never silently delete history (e.g., customer → invoices).

Mismatched data types between FK and PK columns (e.g., INT vs BIGINT, or different collations), which may fail or hurt join performance.

Forgetting to enable foreign keys in SQLite (PRAGMA foreign_keys = ON).

Not indexing foreign key columns (PostgreSQL, SQL Server, and Oracle do not create them automatically; MySQL InnoDB does). Deleting a parent then scans the child table.

Expecting an explicit NULL in INSERT to use the DEFAULT.

Best Practices

Enforce every business rule you can with PK, FK, UNIQUE, NOT NULL, and CHECK.

Name all constraints.

Default to NO ACTION/RESTRICT; use CASCADE only for true parent-child composition.

Index foreign key columns.

Put UNIQUE constraints on natural keys even when using a surrogate PK.

Interview Relevance

Very high: "PK vs UNIQUE?", "Can a UNIQUE column have NULLs?", "What is referential integrity?", "What does ON DELETE CASCADE do?", "Column-level vs table-level constraints?", "Can a table have multiple primary keys?" (No — one PK, which may be composite.)

Practice Questions

Create library_books(book_id PK, isbn UNIQUE NOT NULL, title NOT NULL, copies >= 0).

Create loans referencing library_books so that a book with active loans cannot be deleted.

Explain the difference between RESTRICT and CASCADE with an example.

Will INSERT INTO accounts VALUES (4, 'FD', NULL, DATE '2026-01-01', NULL) succeed? Why?

Write a CHECK that ensures end_date is after start_date or NULL.

In SQL Server, how would you allow many NULLs in a column that must otherwise be unique?

Quiz Checkpoint 1 (Modules 1 – 5)

Q1. (Multiple choice) Which command removes a table's structure and data? A) DELETE B) TRUNCATE C) DROP D) REMOVE

Answer: C. DROP removes the table object. TRUNCATE removes rows but keeps the structure; DELETE removes rows selectively.

Q2. (True/False) A table can have more than one candidate key.

Answer: True. For example, student_id, roll_no, and email can all be candidate keys; one becomes the primary key and the others are alternate keys.

Q3. (True/False) TRUNCATE can always be rolled back.

Answer: False. It can be rolled back inside a transaction in PostgreSQL and SQL Server, but MySQL and Oracle commit implicitly.

Q4. (Predict the output) Answer: 1. A CHECK constraint rejects only FALSE. NULL > 0 is UNKNOWN, so the row is accepted.

Q5. (Find the error) Answer: Not a syntax error, but a design error: money should be DECIMAL(p,s), not FLOAT; dates should be DATE, not VARCHAR.

Q6. (Write the query) Add a column gst_no VARCHAR(15) to customers and make it unique.

Q7. (Multiple choice) Which is a DCL command? A) COMMIT B) GRANT C) MERGE D) ALTER

Answer: B. GRANT and REVOKE are DCL. COMMIT is TCL, MERGE is DML, ALTER is DDL.

Q8. (Multiple choice) Which data type is best for storing an invoice total? A) FLOAT B) REAL C) DECIMAL(12,2) D) VARCHAR(20)

Answer: C. DECIMAL is exact; floating types introduce rounding errors.

CREATE TABLE t (id INT PRIMARY KEY, qty INT CHECK (qty > 0));
INSERT INTO t VALUES (1, NULL);
SELECT COUNT(*) FROM t;

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    amount FLOAT NOT NULL,
    paid_on VARCHAR(10)
);

ALTER TABLE customers ADD gst_no VARCHAR(15);                -- add COLUMN keyword optional in PostgreSQL/MySQL
ALTER TABLE customers ADD CONSTRAINT uq_customers_gst UNIQUE (gst_no);