SQL Constraints Interview Questions and Answers

Constraints are the SQL topic that reveals whether you think about data integrity as the database's job or the application's. Interviewers across backend, data-engineering and database roles use them to test whether you can enforce correctness declaratively — and whether you understand the ripple effects of foreign keys when data is deleted. Every real schema is built on these rules.

The examples use customers(id, email) and orders(id, customer_id, amount, status) and standard SQL that runs on PostgreSQL, MySQL 8+, SQL Server and Oracle unless noted.

What is a constraint and why does it matter?

A constraint is a declarative rule the database enforces on every write, guaranteeing data validity regardless of which application or query performs the change. The six kinds are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT.

The core argument to lead with: a constraint is enforced by the engine on all write paths, so it cannot be bypassed the way application validation can. That single sentence answers most "why use constraints" follow-ups.

CREATE TABLE customers (
  id    INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

Q1. Primary key vs unique key — what are the differences?

A primary key is the row's identity: unique, non-null, and exactly one per table. A unique key enforces uniqueness on other columns, allows NULLs (per the engine's rules), and you can have several.

CREATE TABLE customers (
  id    INT PRIMARY KEY,          -- one identity, no NULLs
  email VARCHAR(255) UNIQUE,      -- additional uniqueness, allows a NULL
  phone VARCHAR(20)  UNIQUE
);

The NULL behaviour is the classic follow-up: standard SQL and most engines allow multiple NULLs in a unique column (because NULL ≠ NULL), though SQL Server historically allowed only one. Knowing that primary key = unique + not null, and naming the NULL nuance, covers the question fully.

Interview note: Trap: "how many NULLs can a UNIQUE column hold?" In PostgreSQL/Oracle/MySQL, multiple (each NULL is distinct); classic SQL Server unique index allowed just one. Dialect-specific — say so.

Q2. Explain foreign keys and referential integrity.

A foreign key makes a column reference a unique/primary key in another table, so every child value must match an existing parent row (or be NULL). This enforces referential integrity — no orphan orders pointing to a missing customer.

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Attempting to insert an order with a customer_id that doesn't exist is rejected; attempting to delete a customer who has orders is (by default) rejected too. That two-way protection is the essence of the answer.

Interview note: Follow-up: "can a foreign key be NULL?" Yes, unless the column is also NOT NULL — a NULL FK means "no parent", which is valid. A non-null FK must always point to a real parent.

Q3. What are the ON DELETE / ON UPDATE referential actions?

They define what happens to child rows when a referenced parent row is deleted or its key changes: CASCADE (propagate), SET NULL (null the child FK), SET DEFAULT, and RESTRICT/NO ACTION (block the operation).

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE      -- deleting a customer deletes their orders
    ON UPDATE CASCADE
);

Choose by domain meaning: order lines cannot exist without their order → CASCADE; an employee's tasks should survive the employee leaving → SET NULL; financial records must never auto-delete → RESTRICT. Framing the choice around whether a child can exist without its parent is the senior answer.

Interview note: Trap: "difference between RESTRICT and NO ACTION?" Both block the delete; RESTRICT checks immediately, NO ACTION can defer the check to end of statement/transaction. Practically similar, subtly different in timing.

Q4. What is a CHECK constraint?

A CHECK constraint enforces a boolean condition on each row, rejecting inserts/updates that violate it — perfect for domain rules like non-negative amounts or a limited set of statuses.

ALTER TABLE orders
  ADD CONSTRAINT chk_amount CHECK (amount >= 0),
  ADD CONSTRAINT chk_status CHECK (status IN ('NEW','PAID','SHIPPED','CANCELLED'));

CHECK is the declarative alternative to a validation trigger for single-row conditions, and it is faster and clearer. Note the limitation interviewers probe: a CHECK generally cannot reference other tables or run subqueries (portably), so cross-table rules still need a foreign key or trigger.

Interview note: Follow-up: "how does a CHECK treat NULL?" A CHECK passes when the condition is NOT false — so a NULL that makes the condition UNKNOWN is allowed. Add NOT NULL if you need the value present.

Q5. NOT NULL vs DEFAULT — how do they interact?

NOT NULL forbids missing values; DEFAULT supplies a value when none is given. Together they guarantee a column is always populated even on inserts that omit it.

CREATE TABLE orders (
  id      INT PRIMARY KEY,
  status  VARCHAR(20) NOT NULL DEFAULT 'NEW',
  created TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

The interaction to explain: DEFAULT only applies when the column is omitted or explicitly set to DEFAULT; inserting an explicit NULL into a NOT NULL column still fails. So DEFAULT convenience and NOT NULL enforcement are complementary, not redundant.

Interview note: Trap: "does DEFAULT fire on an explicit NULL insert?" No — passing NULL overrides the default and, with NOT NULL, errors. The default only fills in omitted columns.

Q6. What is a composite key and where is it used?

A composite key is a primary or unique constraint spanning multiple columns; the combination must be unique though each column may repeat. It is standard in junction tables resolving many-to-many relationships.

CREATE TABLE enrollment (
  student_id INT,
  course_id  INT,
  enrolled_at DATE NOT NULL,
  PRIMARY KEY (student_id, course_id),   -- a student can't enroll twice in one course
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id)  REFERENCES courses(id)
);

The pair (student_id, course_id) uniquely identifies each enrollment while allowing many rows per student and per course. Recognizing the junction-table pattern is the concrete payoff — it links constraints to schema design.

Interview note: Follow-up: "composite key column order — does it matter?" For uniqueness, no; but the implicit index follows the column order, so leftmost-prefix rules apply for lookups. Order it by how you query.

Q7. Can constraints be deferred or temporarily disabled?

Some databases support DEFERRABLE constraints that are checked at transaction commit rather than per statement, which lets you insert mutually-referencing rows in any order. Constraints can also be disabled/re-enabled for bulk loads.

-- PostgreSQL: check at commit, so circular references can be inserted
ALTER TABLE orders
  ADD CONSTRAINT fk_cust FOREIGN KEY (customer_id) REFERENCES customers(id)
  DEFERRABLE INITIALLY DEFERRED;

This matters for two real scenarios: inserting rows in a cycle (A references B, B references A), and speeding up large ETL loads by validating once at the end. Mentioning that disabling constraints for a load requires re-validating afterward shows operational maturity.

Interview note: Trap: "is disabling a FK for a load safe?" Only if you trust the data or re-validate on re-enable; otherwise you can commit orphan rows the constraint would have blocked.

Q8. Why not just validate in the application layer?

Because constraints are enforced by the engine on every write path, so no application, script or manual query can bypass them. They are declarative, atomic with the transaction, and self-documenting — the schema states its own rules.

Application validation is still valuable for fast user feedback and richer messages, but it cannot guarantee integrity: a second service, a data migration, or a DBA's ad-hoc UPDATE all sidestep it. The layered answer — constraints for the guarantee, app validation for UX — is what interviewers reward.

-- The DB guarantees this can never be violated, by anyone:
ALTER TABLE orders ADD CONSTRAINT chk_pos CHECK (amount >= 0);

Interview note: Follow-up: "isn't constraint checking slow?" The overhead is small and buys correctness; the cost of a corrupt-data incident dwarfs it. Only in extreme bulk-load cases do you temporarily relax them.

How to prepare

Build the customers/orders schema and try the failing cases yourself: insert an order for a non-existent customer, delete a customer with orders under each ON DELETE action, and insert a negative amount past a CHECK. Watching each rejection makes the referential-integrity rules concrete instead of abstract. Then model a many-to-many relationship with a composite-key junction table — that pattern appears in almost every real schema and in most design interviews.

Pair this with the normalization questions, since keys and normal forms are two sides of good schema design, and the indexes set, because primary and unique constraints create indexes automatically. The SQL learning path covers the integrity model these constraints enforce. A mock interview built around designing a schema is the best rehearsal for the constraint-and-key round.