PRIMARY KEY, UNIQUE, NOT NULL, CHECK (8.0.16+), and FOREIGN KEY on InnoDB enforce data integrity beyond application code.
Constraints example
CREATE TABLE invoices (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
total DECIMAL(12,2) NOT NULL CHECK (total >= 0),
UNIQUE KEY uq_invoices_email (email)
) ENGINE=InnoDB;Practice: Run on practice.
CHECK enforcement
Older MySQL ignored CHECK—verify version; still validate in app for portability.
Important interview questions and answers
- Q: UNIQUE on email?
A: Prevents duplicate accounts at storage layer. - Q: NOT NULL?
A: SQL rejects missing values—pair with sensible DEFAULT where optional.
Self-check
- What does CHECK total >= 0 do?
- Why UNIQUE on email?
Tip: CHECK constraints need MySQL 8.0.16+—still validate in app.
Interview prep
- UNIQUE email?
Prevents duplicate emails at database layer.
- CHECK MySQL 8?
Enforced from 8.0.16+—verify version.