SQL · Theory

CHECK CONSTRAINT

Theory

273/439

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.

Exam tip

CHECK (marks BETWEEN 0 AND 100) — MySQL 8.0.16+.

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.

Short notes

DefCHECK is a rule on column values (8.0.16+ enforced).
Rulemarks BETWEEN 0 AND 100.
TrapCHECK on old MySQL did nothing.

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

CHECK CONSTRAINT — sample query

Short notes

Questions

CHECK CONSTRAINT

CHECK CONSTRAINT — sample query

Short notes

Questions