SQL · Theory

Constraints

Theory

31/439

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.

Exam tip

PK / FK / UNIQUE / NOT NULL. CHECK on MySQL 8. InnoDB for FK.

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.

Short notes

DefConstraint = table rule (PK, FK, UNIQUE, NOT NULL, CHECK).
RuleLet the database reject bad rows.
TrapCHECK ignored on very old MySQL. FK needs InnoDB.

SQL · Theory

Constraints

← All stacks

Theory

31/439

Constraints

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.

Exam tip

PK / FK / UNIQUE / NOT NULL. CHECK on MySQL 8. InnoDB for FK.

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.

Short notes

DefConstraint = table rule (PK, FK, UNIQUE, NOT NULL, CHECK).
RuleLet the database reject bad rows.
TrapCHECK ignored on very old MySQL. FK needs InnoDB.

Constraints

Constraints — sample query

Short notes

Questions

Constraints

Constraints — sample query

Short notes

Questions