✏️ Explanatory Question
Level: Basic — Constraints are the rules that keep your data valid and consistent.
A constraint is a rule applied to a column or table that restricts the type of data that can go into it. Constraints enforce data integrity and accuracy — if an operation violates a constraint, MySQL rejects it.
CHECK constraint was parsed but ignored. From 8.0.16 onward, it is fully enforced — a common interview "gotcha".
| Constraint | Purpose | NULL Allowed? |
|---|---|---|
| NOT NULL | Forbids empty values | No |
| UNIQUE | No duplicate values | Yes (one NULL) |
| PRIMARY KEY | Unique row identifier | No |
| FOREIGN KEY | Links tables | Yes |
| CHECK | Condition-based rule | Depends on condition |
| DEFAULT | Fallback value | N/A |
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY + AUTO_INCREMENT
emp_name VARCHAR(100) NOT NULL, -- NOT NULL
email VARCHAR(100) UNIQUE, -- UNIQUE
age INT CHECK (age >= 18), -- CHECK
status VARCHAR(10) DEFAULT 'Active', -- DEFAULT
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id) -- FOREIGN KEY
);