What do the NOT NULL, DEFAULT and CHECK constraints do?
SHORT ANSWER
NOT NULL forbids missing values, DEFAULT supplies a value when none is given on insert, and CHECK rejects rows whose values fail a condition. Together they keep bad data out at the database level.
Explain it simply
A form can say "this box is mandatory" (NOT NULL), "if left blank, write India" (DEFAULT) and "age must be 18 or more" (CHECK). The database enforces these rules for every application that writes to it.
Example
sql
CREATE TABLE members (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
country VARCHAR(30) DEFAULT 'India',
age INT CHECK (age >= 18),
status VARCHAR(10) DEFAULT 'active'
CHECK (status IN ('active', 'blocked'))
);
INSERT INTO members (id, name, age) VALUES (1, 'Asha', 20);
-- country becomes 'India', status becomes 'active'
INSERT INTO members (id, name, age) VALUES (2, 'Ravi', 16);
-- fails, the CHECK on age is violatedDetails:
- DEFAULT applies only when the column is omitted, not when you explicitly insert NULL into a nullable column.
- A CHECK passes when the condition is TRUE or unknown (NULL). So
ageas NULL passes the CHECK; add NOT NULL if it must be present. - MySQL enforces CHECK only from version 8.0.16; earlier versions parsed and ignored it.
What to say in the interview
"These constraints guard data integrity in the schema. NOT NULL requires a value, DEFAULT fills one in, CHECK validates a condition. Note that CHECK accepts NULL unless NOT NULL is also set."
Common mistakes
- Assuming CHECK rejects NULLs.