SQL · Theory

CHECK CONSTRAINT

← All stacks

Theory

273/439

CHECK CONSTRAINT

If you can teach CHECK CONSTRAINT using lookup by roll, you know it. If you only know the heading, you don’t.

CHECK CONSTRAINT on the board — PRIMARY KEY(roll) + INDEX. Then say what lookup by roll looks like after.

CHECK CONSTRAINT exists so Zara can keep lookup by roll correct in a big table.

CHECK CONSTRAINT shows up in a big table. Name lookup by roll, not “a real-world scenario”.

CHECK CONSTRAINT trap — indexing a low-cardinality flag only. Zara loses marks for that every viva.

Place CHECK CONSTRAINT next to nearby SQL work — PRIMARY KEY(roll) + INDEX is the link.

Close CHECK CONSTRAINT with — “If I skip it, lookup by roll goes wrong like this: indexing a low-cardinality flag only.”

Exam tip

Say CHECK CONSTRAINT in one breath, then PRIMARY KEY(roll) + INDEX, then indexing a low-cardinality flag only.

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 CONSTRAINT — Zara uses it for lookup by roll in a big table.
RuleCHECK CONSTRAINT → PRIMARY KEY(roll) + INDEX.
RememberCHECK CONSTRAINT + the query planner (a big table).
UseCHECK CONSTRAINT in a big table (lookup by roll).
TrapCHECK CONSTRAINT — indexing a low-cardinality flag only.
ExCHECK CONSTRAINT → lookup by roll.

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