Constraints & Integrity Interview Questions & Answers

A constraint is a rule enforced by the database engine that limits what values can be stored in a column or set of columns. Constraints catch bad data at write time — before it ever enters the database — so application code never has to defensively re-validate what the schema already guarantees.

CREATE TABLE employees (
  id         INT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- unique, not null
  email      TEXT NOT NULL UNIQUE,                          -- no nulls, no dupes
  salary     NUMERIC(10,2) CHECK (salary > 0),             -- must be positive
  dept_id    INT  REFERENCES departments(id)               -- must exist in parent
);

Rule of thumb: encode every invariant you can in the schema. A constraint that runs in 0 ms at insert time is cheaper than debugging corrupt data in production.

A PRIMARY KEY uniquely identifies each row in a table. It is a combination of two implied constraints: UNIQUE (no two rows share the same value) and NOT NULL (the key column(s) can never be NULL). Each table can have at most one primary key.

-- Single-column PK
CREATE TABLE users (
  id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

-- Composite PK (natural key)
CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

The database automatically creates a unique index on the PK column(s), which makes lookups by PK fast.

Rule of thumb: every table should have a primary key. Prefer a single surrogate integer/UUID PK; use composite PKs for pure join tables (many-to-many mappings).

A FOREIGN KEY (FK) constraint ensures that every non-NULL value in the referencing column exists in the referenced column of the parent table. It enforces referential integrity — you cannot have an order that points to a customer_id that does not exist.

CREATE TABLE orders (
  id          INT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INT  NOT NULL REFERENCES customers(id),
  total       NUMERIC(10,2) NOT NULL
);

-- Explicit form with named constraint
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

On insert, the DB checks that customer_id exists in customers.id. On delete of a customer row, the FK's referential action decides what happens (see CASCADE / RESTRICT question).

Rule of thumb: always add FK constraints on foreign-key columns — they are the database's guarantee that your data is consistent, not just a documentation comment.

Referential actions define what happens to child rows when the parent row is deleted or its PK updated:

Action	Effect on child rows
RESTRICT	Raises an error — cannot delete/update if children exist
NO ACTION	Like RESTRICT but checked at end of statement (default)
CASCADE	Deletes (or updates) all matching child rows automatically
SET NULL	Sets the FK column(s) to NULL in child rows
SET DEFAULT	Sets the FK column(s) to their default value

CREATE TABLE order_items (
  id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   INT NOT NULL
    REFERENCES orders(id) ON DELETE CASCADE,  -- items gone when order deleted
  product_id INT NOT NULL
    REFERENCES products(id) ON DELETE RESTRICT -- block if items still reference product
);

Rule of thumb: use ON DELETE CASCADE for owned child records (items, line-items, comments). Use RESTRICT for shared reference data you never want to accidentally wipe. Avoid SET NULL unless NULL is semantically meaningful in the child.

A UNIQUE constraint ensures that no two rows have the same value(s) in the constrained column(s). Unlike PRIMARY KEY, a table can have multiple unique constraints, and unique columns can contain NULL (Postgres and SQL Server treat each NULL as distinct; MySQL treats NULL = NULL).

CREATE TABLE users (
  id       INT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email    TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  phone    TEXT UNIQUE          -- nullable; two users can have NULL phone
);

-- Multi-column unique constraint
ALTER TABLE team_members
  ADD CONSTRAINT uq_team_user UNIQUE (team_id, user_id);

A UNIQUE constraint creates a unique index automatically, so it also speeds up equality lookups on those columns.

Rule of thumb: add UNIQUE to every natural business key (email, username, SSN) in addition to the surrogate PK — it is the database's guarantee that your deduplication logic is not bypassed.

A CHECK constraint specifies a boolean expression that every row must satisfy. The insert or update is rejected if the expression evaluates to FALSE. (NULL evaluates to UNKNOWN and is allowed through by default.)

CREATE TABLE products (
  id       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  price    NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  discount NUMERIC(5,2)  CHECK (discount BETWEEN 0 AND 100),
  status   TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived'))
);

-- Cross-column check
ALTER TABLE bookings
  ADD CONSTRAINT chk_dates CHECK (check_out > check_in);

Limitations: CHECK expressions cannot reference other tables (use triggers or application logic for cross-table validation). Some databases (MySQL pre-8.0) parsed but silently ignored CHECK constraints.

Rule of thumb: use CHECK to enforce domain rules on a single row (non-negative price, valid status enum, date ordering). Prefer an ENUM type or a FK to a lookup table when the valid set of values is managed by the business and may change.

NOT NULL prevents the column from storing a NULL. An attempt to insert or update a row with NULL in that column raises an error.

CREATE TABLE events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        TEXT    NOT NULL,            -- must always be present
  description TEXT,                        -- optional (nullable)
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Add NOT NULL when:

• The value is required for every row to be meaningful.
• You need aggregate functions (like SUM, AVG) to behave predictably (NULL is skipped by aggregates).

Omit NOT NULL (allow NULL) when:

• The value is genuinely optional ("middle name", "description").
• You want to distinguish "not yet provided" from a default value.

Rule of thumb: default to NOT NULL; make a column nullable only when the absence of a value has a distinct meaning you intend to query for.

By default, constraints are checked immediately after each statement. A deferrable constraint can be postponed until COMMIT, which is necessary when two statements in the same transaction temporarily violate the constraint before reaching a consistent state.

-- Postgres: declare the FK as deferrable
ALTER TABLE nodes
  ADD CONSTRAINT fk_parent
  FOREIGN KEY (parent_id) REFERENCES nodes(id)
  DEFERRABLE INITIALLY DEFERRED;

-- Now you can insert in any order within a transaction
BEGIN;
  INSERT INTO nodes (id, parent_id) VALUES (2, 1);  -- parent 1 doesn't exist yet
  INSERT INTO nodes (id, parent_id) VALUES (1, NULL);
COMMIT;  -- FK checked here — both rows now exist, so it passes

Common use case: self-referential trees, circular FK graphs, or bulk imports where you cannot guarantee insert order.

Rule of thumb: use DEFERRABLE INITIALLY DEFERRED for FK constraints in self-referential or circular relationship tables. Leave constraints NOT DEFERRABLE by default — immediate checking catches bugs faster.

An exclusion constraint (Postgres-specific) generalizes UNIQUE to arbitrary operators, not just equality. It ensures that for any two rows, at least one of the specified conditions is false. The classic use case is preventing overlapping time ranges.

-- Requires btree_gist extension for mixed operator support
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
  room_id    INT  NOT NULL,
  reserved   TSTZRANGE NOT NULL,  -- time range type
  EXCLUDE USING GIST (
    room_id  WITH =,              -- same room
    reserved WITH &&              -- overlapping periods
  )
);

-- This pair of inserts will succeed
INSERT INTO room_bookings VALUES (1, '[2026-06-01, 2026-06-03)');
INSERT INTO room_bookings VALUES (1, '[2026-06-05, 2026-06-07)');

-- This overlaps and will fail the exclusion constraint
INSERT INTO room_bookings VALUES (1, '[2026-06-02, 2026-06-06)');

Rule of thumb: use exclusion constraints for scheduling, resource allocation, or any domain where overlapping intervals must be prevented. They are more expressive than triggers for this pattern.

When you omit a name, the database generates one automatically (orders_customer_id_fkey in Postgres, a UUID-based name in SQL Server). Unnamed constraints are hard to reference in migrations, error messages, and application code.

-- Anonymous (avoid)
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Named (preferred)
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Now you can drop it cleanly
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;

A consistent naming convention (fk_<table>_<col>, uq_<table>_<col>, chk_<table>_<rule>) makes the schema self-documenting and makes migration scripts reliable across environments.

Rule of thumb: always name constraints explicitly with a predictable convention. Anonymous constraints force you to query the system catalog every time you need to alter or drop one.

A partial unique index applies the uniqueness guarantee only to rows that satisfy a WHERE condition. This is useful when uniqueness should apply only to a subset of rows — for example, only active records.

-- Only one active record per user (soft-delete pattern)
CREATE UNIQUE INDEX uq_subscriptions_active_user
  ON subscriptions (user_id)
  WHERE cancelled_at IS NULL;

-- Two rows for user 1, one cancelled and one active — both allowed
INSERT INTO subscriptions (user_id, cancelled_at) VALUES (1, '2025-01-01');
INSERT INTO subscriptions (user_id, cancelled_at) VALUES (1, NULL);  -- OK

-- A second active row for user 1 — fails
INSERT INTO subscriptions (user_id, cancelled_at) VALUES (1, NULL);  -- ERROR

Rule of thumb: use a partial unique index when the uniqueness rule applies to a subset of rows (e.g., non-deleted, active-status). Full UNIQUE constraints cannot express this; partial indexes can.

In SQL Server you can DISABLE a foreign key or check constraint and later ENABLE it, optionally with WITH NOCHECK (skip validation of existing data) or WITH CHECK (validate existing data on re-enable).

In Postgres, you can SET CONSTRAINTS ALL DEFERRED for the current transaction, or ALTER TABLE … DISABLE TRIGGER ALL to disable trigger-based constraints. You can also drop and recreate constraints for bulk loads.

In MySQL, SET FOREIGN_KEY_CHECKS = 0 disables FK checks session-wide.

-- SQL Server bulk load workaround
ALTER TABLE orders NOCHECK CONSTRAINT fk_orders_customer;
BULK INSERT orders FROM 'orders.csv';
ALTER TABLE orders WITH CHECK CHECK CONSTRAINT fk_orders_customer;

-- MySQL bulk load
SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA INFILE 'orders.csv' INTO TABLE orders;
SET FOREIGN_KEY_CHECKS = 1;

Rule of thumb: disabling constraints is acceptable for controlled bulk loads, but always re-enable and validate immediately. Never disable constraints permanently — you will eventually have corrupt data.

A UNIQUE constraint is enforced by a unique index under the hood. In Postgres and SQL Server, a UNIQUE constraint and a CREATE UNIQUE INDEX on the same column are nearly equivalent — the constraint simply gives the index a constraint-associated name.

-- These two are functionally equivalent in Postgres:
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

CREATE UNIQUE INDEX uq_users_email ON users (email);

Differences in Postgres:

• Constraints can be DEFERRABLE; indexes cannot.
• You can DROP CONSTRAINT but must DROP INDEX for a standalone index.
• Partial unique indexes cannot be declared as a UNIQUE constraint (you need the CREATE UNIQUE INDEX … WHERE form).

Rule of thumb: use the CONSTRAINT … UNIQUE form when you need deferred checking or want the DDL to be clearly declarative. Use CREATE UNIQUE INDEX when you need a partial unique condition.

Each constraint violation raises a specific error that application code can catch and present meaningfully:

Constraint	Postgres SQLSTATE	Typical message
NOT NULL	23502	null value in column "x" violates not-null constraint
UNIQUE	23505	duplicate key value violates unique constraint "uq_…"
FOREIGN KEY	23503	insert or update on table "x" violates foreign key constraint
CHECK	23514	new row for relation "x" violates check constraint "chk_…"

# Python + psycopg2 example
from psycopg2 import errors
try:
    cur.execute("INSERT INTO users (email) VALUES (%s)", (email,))
except errors.UniqueViolation:
    return {"error": "That email is already registered"}

Rule of thumb: catch constraint violations by SQLSTATE code, not by parsing the error message string — message text can change between database versions. Map each violation to a user-friendly message in the application layer.

A column-level constraint is declared inline with the column definition and can only reference that single column. A table-level constraint is declared separately (after all column definitions) and can reference multiple columns.

CREATE TABLE order_items (
  order_id   INT NOT NULL,               -- column-level NOT NULL
  product_id INT NOT NULL,               -- column-level NOT NULL
  quantity   INT NOT NULL CHECK (quantity > 0),  -- column-level CHECK

  -- Table-level: composite PK (must reference both columns — impossible column-level)
  PRIMARY KEY (order_id, product_id),

  -- Table-level: cross-column check
  CONSTRAINT chk_valid_quantity CHECK (quantity <= 1000 OR order_id < 1000)
);

Rule of thumb: use column-level constraints for single-column rules (NOT NULL, UNIQUE, CHECK on one column). Use table-level constraints for composite keys, composite unique indexes, and cross-column CHECK expressions.