CHECK CONSTRAINT — SQL Technical Interview Questions & Answers

CHECK CONSTRAINT

CHECK CONSTRAINT

  • CHECK (marks BETWEEN 0 AND 100) rejects out-of-range values. Enforced in MySQL 8.0.16+. Older versions accepted the syntax and ignored it — a famous trap.
  • Keep CHECKs simple. Heavy rules belong in the app + CHECK as a last line of defence.
  • Trap: promising CHECK on 5.7.
  • On the example next to this theory: CHECK CONSTRAINT — keys and constraints protect data integrity; indexes speed lookups.

Technical Interview Tip: CHECK (marks BETWEEN 0 AND 100) — MySQL 8.0.16+.

Code Examples for CHECK CONSTRAINT

CHECK CONSTRAINT — sample query

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);
INSERT INTO authors VALUES (1, 'a@x.com');
INSERT INTO books VALUES (1, 1, 'SQL Basics');
SELECT * FROM books;

CHECK CONSTRAINT — keys and constraints protect data integrity; indexes speed lookups.

How to Explain CHECK CONSTRAINT to an Interviewer

Did old MySQL enforce CHECK?

No, it ignored it 8.0.16+ enforces