SQL constraints interview questions, with answers

The questions, with answers

1. 1.What are the standard SQL constraints, and what does each enforce?

   Six, and you should be able to list them in one breath. NOT NULL: the column must have a value. UNIQUE: no two rows share a value (NULLs usually excepted). PRIMARY KEY: UNIQUE plus NOT NULL, one per table, the row's identity. FOREIGN KEY: the value must exist in a referenced key of another table — referential integrity. CHECK: a boolean condition every row must satisfy, such as quantity > 0. DEFAULT: the value used when an INSERT omits the column — strictly a column property rather than a rule, but always asked alongside the others. The theme to state: constraints live in the database so that every application, script and console session obeys the same rules, instead of trusting each one to validate.

   CREATE TABLE Orders (
       order_id    INT PRIMARY KEY,
       customer_id INT NOT NULL REFERENCES Customers(customer_id),
       quantity    INT NOT NULL CHECK (quantity > 0),
       status      VARCHAR(20) NOT NULL DEFAULT 'new',
       tracking_no VARCHAR(40) UNIQUE
   );

2. 2.Does a DEFAULT stop a NULL from being inserted?

   No. A DEFAULT only fills a column that the INSERT leaves out entirely. If the statement names the column and supplies NULL explicitly, the NULL is what gets stored — unless the column is also NOT NULL, in which case the statement fails. So balance DECIMAL NOT NULL DEFAULT 0 does two different jobs: DEFAULT covers INSERT INTO Accounts (account_id) VALUES (1), and NOT NULL rejects INSERT INTO Accounts (account_id, balance) VALUES (2, NULL). Candidates often assume DEFAULT means "replace NULL with 0"; it means "use 0 when nothing at all is given". Some databases let you write the keyword DEFAULT in place of a value to ask for it explicitly.

3. 3.What happens when a CHECK constraint evaluates to NULL?

   The row is accepted. A CHECK constraint rejects a row only when its condition is FALSE; UNKNOWN — which is what any comparison involving NULL produces — passes. So CHECK (quantity > 0) happily stores a row with quantity NULL, because NULL > 0 is not false. If the column must be present as well as positive, add NOT NULL; the two constraints are independent. This is the opposite of how WHERE treats UNKNOWN, and it is the single most common trick question on constraints. One more edge: MySQL parsed CHECK constraints for years but silently ignored them until 8.0.16, so on an older MySQL a CHECK enforces nothing at all.

4. 4.How do you add or drop a constraint on a table that already exists?

   With ALTER TABLE. Give the constraint a name so you can drop it later: ALTER TABLE Orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES Customers(customer_id). Dropping is ALTER TABLE Orders DROP CONSTRAINT fk_orders_customer — in MySQL the keyword depends on the kind: DROP FOREIGN KEY, DROP INDEX for a unique key, DROP CHECK. NOT NULL is the exception: it is a column attribute, so it is ALTER TABLE ... ALTER COLUMN ... SET NOT NULL in PostgreSQL and MODIFY COLUMN in MySQL. Unnamed constraints get a generated name you have to look up in the catalogue first, which is the practical reason to name every constraint you create.

   ALTER TABLE Orders
     ADD CONSTRAINT chk_orders_qty CHECK (quantity > 0);
   
   ALTER TABLE Orders
     DROP CONSTRAINT chk_orders_qty;      -- MySQL: DROP CHECK chk_orders_qty

5. 5.What happens when you add a constraint and existing rows violate it?

   The ALTER TABLE fails: the database checks every existing row before accepting the constraint, and if any row breaks it the whole statement is rejected and nothing changes. So adding a foreign key to Orders with a customer_id that matches no customer, or a CHECK on a column with negative values already in it, means cleaning the data first. Two escape hatches exist. PostgreSQL lets you add a CHECK or FOREIGN KEY as NOT VALID, which enforces it for new writes only and lets you VALIDATE CONSTRAINT later once the data is fixed. MySQL has a session flag, foreign_key_checks = 0, that skips the check — dangerous, because the bad rows stay bad. Mention that the honest fix is a query to find the offending rows, then a corrective update.

6. 6.What happens to the statement and the transaction when a constraint is violated?

   The statement fails with an error and its changes are rolled back — for a multi-row INSERT that means none of the rows are inserted, not just the bad one. What happens to the surrounding transaction depends on the database: in PostgreSQL the transaction is marked aborted and every later statement fails until you ROLLBACK, while in MySQL and SQL Server the transaction stays open and you may continue after handling the error. Interviewers often ask how to insert what you can: filter the bad rows out in the SELECT that feeds the INSERT, or use an upsert form — INSERT ... ON CONFLICT DO NOTHING in PostgreSQL, INSERT IGNORE in MySQL — when the violation you expect is a duplicate key.

7. 7.What is a deferrable constraint?

   A constraint whose check is postponed until the transaction commits rather than run after each statement. It solves ordering problems that immediate checking makes impossible: two rows that reference each other through foreign keys, or a re-numbering that temporarily makes two rows share a unique value. PostgreSQL supports DEFERRABLE INITIALLY DEFERRED on UNIQUE, PRIMARY KEY, FOREIGN KEY and EXCLUDE constraints (its CHECK and NOT NULL are always checked immediately); Oracle allows it on every kind of constraint; MySQL and SQL Server have no deferred checking at all. The trade-off: a deferred violation surfaces at COMMIT, far from the statement that caused it, so use it only for the cases that need it.

8. 8.When should a rule be a constraint, and when should it be a trigger or application code?

   Make it a constraint whenever a constraint can express it: constraints are declarative, the optimiser understands them, they are enforced for every writer, and they are cheaper than a trigger firing per row. Use a trigger when the rule involves other rows or other tables in ways a CHECK cannot — a CHECK can only see the row being written, so "no overlapping bookings for the same room" needs a trigger or, in PostgreSQL, an exclusion constraint. Application code is for rules that are business policy rather than data integrity — a discount cap that changes next quarter — because those change too often for a migration each time. The interview line: the database guarantees the data can never be wrong; the application decides what is allowed today.

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 5 on constraints and 60 across SQL.