Constraints — SQL Technical Interview Questions & Answers

Constraints

Constraints

  • A constraint is a rule the table must obey. PRIMARY KEY: unique + not null. FOREIGN KEY: this roll must exist in students. UNIQUE: email not repeated. NOT NULL: name required. CHECK (marks BETWEEN 0 AND 100): MySQL 8.0.16+ enforces CHECK; older versions parsed it and ignored it — say that if asked.
  • Constraints protect you from bad INSERT/UPDATE. Better a clear error than silent junk data.
  • Trap: putting all rules only in PHP and none in the database. Two apps will disagree. Trap: FOREIGN KEY on InnoDB only — MyISAM will ignore FKs.
  • On the example next to this theory: Constraints: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: PK / FK / UNIQUE / NOT NULL. CHECK on MySQL 8. InnoDB for FK.

Code Examples for Constraints

Constraints — sample query

-- Constraints
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Constraints: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Constraints to an Interviewer

Name four constraints.

PRIMARY KEY FOREIGN KEY UNIQUE NOT NULL

Why FOREIGN KEY?

Child roll must exist in parent Stops orphan marks rows