SQL Constraints and DEFAULT
Constraints are rules the database enforces on every insert and update, so bad data never gets in, no matter which application, script or person writes it. This lesson shows all six MySQL constraints on one table, what each error looks like, and covers DEFAULT in detail. Each constraint then has its own lesson.
- The six constraints and what each one prevents
- Column-level vs table-level and named constraints
- Give columns default values with
DEFAULT - List, add and drop constraints
The six constraints
| Constraint | Rule | Lesson |
|---|---|---|
NOT NULL | the column must have a value | NOT NULL |
UNIQUE | no two rows may share a value | UNIQUE |
PRIMARY KEY | unique + not null; identifies each row | PRIMARY KEY |
FOREIGN KEY | the value must exist in another table | FOREIGN KEY |
CHECK | the value must satisfy a condition | CHECK |
DEFAULT | value used when none is given | this lesson |
All of them in one table
CREATE TABLE members ( member_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, full_name VARCHAR(60) NOT NULL, dept_id INT, age INT CHECK (age >= 18), plan VARCHAR(10) NOT NULL DEFAULT 'free', joined_on DATE DEFAULT (CURRENT_DATE), CONSTRAINT fk_member_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO members (member_id, email, full_name, dept_id, age)VALUES (1, 'asha@example.com', 'Asha Rao', 1, 30);SELECT member_id, email, plan, joined_on IS NOT NULL AS has_join_date FROM members;| member_id | plan | has_join_date | |
|---|---|---|---|
| 1 | asha@example.com | free | 1 |
PRIMARY KEYonmember_id,NOT NULL UNIQUEonemail,CHECKonage, a namedFOREIGN KEYondept_id.- The insert leaves out
planandjoined_on, so their defaults are used:'free'and today’s date. - The
SELECTconfirms the defaults were applied.
What each violation looks like
Each of these inserts breaks one rule (every block starts from the table above with Asha’s row):
CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, full_name VARCHAR(60) NOT NULL, dept_id INT, age INT CHECK (age >= 18), CONSTRAINT fk_member_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO members VALUES (1, 'asha@example.com', 'Asha Rao', 1, 30);INSERT INTO members VALUES (2, 'asha@example.com', 'Second Asha', 1, 25);ERROR 1062 (23000): Duplicate entry 'asha@example.com' for key 'members.email'
CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, full_name VARCHAR(60) NOT NULL, dept_id INT, age INT CHECK (age >= 18), CONSTRAINT fk_member_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO members VALUES (3, 'teen@example.com', 'Young User', 1, 15);ERROR 3819 (HY000): Check constraint 'members_chk_1' is violated.
CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, full_name VARCHAR(60) NOT NULL, dept_id INT, age INT CHECK (age >= 18), CONSTRAINT fk_member_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO members VALUES (4, 'x@example.com', 'Nobody', 42, 30);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`members`, CONSTRAINT `fk_member_dept` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))
- Error 1062: the email is already used (
UNIQUE). - Error 3819: age 15 fails
CHECK (age >= 18). - Error 1452: there is no department 42 (
FOREIGN KEY).
Column-level vs table-level
A constraint written next to a column applies to that column. A constraint written as a separate line (table-level) can cover several columns and can be named:
CREATE TABLE enrolments ( student_id INT, course_id INT, enrolled DATE NOT NULL, CONSTRAINT pk_enrolments PRIMARY KEY (student_id, course_id), CONSTRAINT chk_enrolled CHECK (enrolled >= '2020-01-01'));Name your constraints (fk_member_dept, chk_age). The name appears in error messages and is what you use to drop the constraint later.
DEFAULT
DEFAULT gives a column a value when an INSERT does not supply one. Since MySQL 8.0.13 a default can be an expression in parentheses:
CREATE TABLE tasks ( task_id INT PRIMARY KEY, title VARCHAR(50) NOT NULL, status VARCHAR(12) NOT NULL DEFAULT 'open', priority INT DEFAULT 3, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, code VARCHAR(40) DEFAULT (UUID()));INSERT INTO tasks (task_id, title) VALUES (1, 'Write test plan');INSERT INTO tasks (task_id, title, status, priority) VALUES (2, 'Fix login bug', 'in_progress', 1);INSERT INTO tasks (task_id, title, priority) VALUES (3, 'Review', DEFAULT);SELECT task_id, title, status, priority, created_at IS NOT NULL AS stamped, CHAR_LENGTH(code) AS code_len FROM tasks;| task_id | title | status | priority | stamped | code_len |
|---|---|---|---|---|---|
| 1 | Write test plan | open | 3 | 1 | 36 |
| 2 | Fix login bug | in_progress | 1 | 1 | 36 |
| 3 | Review | open | 3 | 1 | 36 |
- Task 1 gives only id and title: status
'open', priority 3, a timestamp and a generated UUID are filled in. - Task 2 gives its own status and priority, so the defaults are not used for those.
- Task 3 writes the keyword
DEFAULTexplicitly, which also uses the default value.
An explicit NULL is not replaced by the default: INSERT ... (priority) VALUES (NULL) stores NULL. Add NOT NULL if that must not happen.
See a table’s constraints
SELECT constraint_name, constraint_typeFROM information_schema.table_constraintsWHERE table_schema = 'company' AND table_name = 'employees';| CONSTRAINT_NAME | CONSTRAINT_TYPE |
|---|---|
| PRIMARY | PRIMARY KEY |
| employees_ibfk_1 | FOREIGN KEY |
SHOW CREATE TABLE employees; shows all constraints in the original syntax.
Add and drop constraints later
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);ALTER TABLE products ALTER stock SET DEFAULT 0;SELECT constraint_name, constraint_type FROM information_schema.table_constraintsWHERE table_schema = 'company' AND table_name = 'products';ALTER TABLE products DROP CHECK chk_price;| CONSTRAINT_NAME | CONSTRAINT_TYPE |
|---|---|
| PRIMARY | PRIMARY KEY |
| chk_price | CHECK |
Adding a constraint fails if existing rows already break it, so clean the data first.
Quick quiz
1. Which constraint makes sure a value exists in another table?
A foreign key references the primary key of another table.
2. An INSERT gives NULL explicitly for a column with DEFAULT 3. What is stored?
DEFAULT is used only when the column is left out (or the DEFAULT keyword is given).
3. Why name a constraint?
MySQL generates names like members_chk_1 otherwise.
Constraints FAQ
What are constraints in SQL?
Rules defined on columns or tables that the database enforces: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT.
What is the difference between a primary key and a unique key?
Both prevent duplicates. A table has only one primary key and it cannot be NULL; it can have many unique keys, which allow NULLs.
Can a DEFAULT value be a function?
Yes, since MySQL 8.0.13, as an expression in parentheses, e.g. DEFAULT (UUID()) or DEFAULT (CURRENT_DATE). CURRENT_TIMESTAMP works without parentheses for DATETIME and TIMESTAMP.