MySQL Constraints

Last updated: October 19, 2026Oct 19, 2026 6 min read

Constraints are rules attached to tables and columns that MySQL enforces on every INSERT and UPDATE. They stop bad data at the door, such as a patient without a name, two patients with the same ID, a negative bill or an appointment for a doctor who doesn't exist. Because the database enforces them, every application that writes to it is protected.

1. Why Use Constraints?

Figure 1: constraints are rules the database enforces on every change, whatever program sends it.

Correctness: invalid data never gets in, so reports and code can trust it.
One place for rules: the Java app, a Python script and someone at the command line all obey the same rules.
Clear design: keys document how tables relate.
Speed: PRIMARY KEY and UNIQUE constraints create indexes that make lookups fast.

2. The Six Constraints in MySQL

Constraint	Rule	Example	Tutorial
`NOT NULL`	Column must have a value	`name VARCHAR(50) NOT NULL`	NOT NULL
`UNIQUE`	No two rows share the value (NULLs allowed)	`email VARCHAR(80) UNIQUE`	UNIQUE
`PRIMARY KEY`	Unique + NOT NULL row identifier, one per table	`patient_id INT PRIMARY KEY`	Primary Key
`FOREIGN KEY`	Value must exist in another table	`REFERENCES doctors(doctor_id)`	Foreign Key
`CHECK`	A condition must be true (8.0.16+)	`CHECK (fee >= 0)`	CHECK
`DEFAULT`	Value used when none is given	`status VARCHAR(10) DEFAULT 'booked'`	DEFAULT

Strictly speaking DEFAULT doesn't reject anything, but it is defined with the column and is usually taught with constraints.

3. All Constraints in One Example

all-constraints.sql

CREATE TABLE doctors (
  doctor_id INT PRIMARY KEY,
  name      VARCHAR(50) NOT NULL,
  reg_no    VARCHAR(12) NOT NULL UNIQUE
);

CREATE TABLE appointments (
  appt_id    INT AUTO_INCREMENT PRIMARY KEY,
  patient    VARCHAR(50) NOT NULL,
  doctor_id  INT NOT NULL,
  appt_date  DATE NOT NULL,
  fee        DECIMAL(8,2) NOT NULL DEFAULT 500,
  status     VARCHAR(10) NOT NULL DEFAULT 'booked',
  CONSTRAINT fk_appt_doctor FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
  CONSTRAINT chk_fee CHECK (fee >= 0),
  CONSTRAINT chk_status CHECK (status IN ('booked', 'done', 'cancelled')),
  CONSTRAINT uq_slot UNIQUE (doctor_id, appt_date, patient)
);

INSERT INTO doctors VALUES (1, 'Dr. Mehta', 'MH-10234'), (2, 'Dr. Iyer', 'TN-55871');
INSERT INTO appointments (patient, doctor_id, appt_date) VALUES ('Anaya', 1, '2026-03-10');
SELECT * FROM appointments;

Result: 1 row

appt_id	patient	doctor_id	appt_date	fee	status
1	Anaya	1	2026-03-10	500.00	booked

The defaults filled in fee and status. Now try to break each rule:

break-the-rules.sql

INSERT INTO appointments (patient, doctor_id, appt_date) VALUES (NULL, 1, '2026-03-11');
INSERT INTO doctors VALUES (3, 'Dr. Rao', 'MH-10234');
INSERT INTO doctors VALUES (1, 'Dr. Khan', 'DL-77001');
INSERT INTO appointments (patient, doctor_id, appt_date) VALUES ('Bose', 9, '2026-03-11');
INSERT INTO appointments (patient, doctor_id, appt_date, fee) VALUES ('Bose', 2, '2026-03-11', -50);
INSERT INTO appointments (patient, doctor_id, appt_date, status) VALUES ('Bose', 2, '2026-03-11', 'maybe');
INSERT INTO appointments (patient, doctor_id, appt_date) VALUES ('Anaya', 1, '2026-03-10');

Output

ERROR 1048 (23000): Column 'patient' cannot be null
ERROR 1062 (23000): Duplicate entry 'MH-10234' for key 'doctors.reg_no'
ERROR 1062 (23000): Duplicate entry '1' for key 'doctors.PRIMARY'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hospital`.`appointments`, CONSTRAINT `fk_appt_doctor` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`doctor_id`))
ERROR 3819 (HY000): Check constraint 'chk_fee' is violated.
ERROR 3819 (HY000): Check constraint 'chk_status' is violated.
ERROR 1062 (23000): Duplicate entry '1-2026-03-10-Anaya' for key 'appointments.uq_slot'

Every bad row was rejected with a clear message, and the table still holds only the one valid appointment.

4. Column-Level vs Table-Level Syntax

Column-level (inside the column definition)	Table-level (after the columns)
`email VARCHAR(80) UNIQUE`	`UNIQUE (email)` or `CONSTRAINT uq_email UNIQUE (email)`
`id INT PRIMARY KEY`	`PRIMARY KEY (id)`, needed for multi-column keys
`fee DECIMAL(8,2) CHECK (fee >= 0)`	`CONSTRAINT chk_fee CHECK (fee >= 0)`, can use several columns
`NOT NULL`, `DEFAULT`: column-level only	—

Name your constraints with CONSTRAINT name … (for example fk_appt_doctor, chk_fee). Error messages then tell you exactly which rule failed, and you can drop the constraint by name later.

5. Listing a Table's Constraints

query.sql

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'hospital' AND table_name = 'appointments'
ORDER BY constraint_type, constraint_name;

Result: 5 rows

CONSTRAINT_NAME	CONSTRAINT_TYPE
chk_fee	CHECK
chk_status	CHECK
fk_appt_doctor	FOREIGN KEY
PRIMARY	PRIMARY KEY
uq_slot	UNIQUE

CHECK constraints have their own view with the condition text:

query.sql

SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'hospital';

Result: 2 rows

CONSTRAINT_NAME	CHECK_CLAUSE
chk_fee	(`fee` >= 0)
chk_status	(`status` in (_utf8mb4\'booked\',_utf8mb4\'done\',_utf8mb4\'cancelled\'))

6. Adding and Dropping Constraints Later

Use ALTER TABLE. Existing rows must already satisfy a new constraint, or it is refused:

add-fails.sql

INSERT INTO appointments (patient, doctor_id, appt_date, fee) VALUES ('Chen', 2, '2026-03-12', 50);
ALTER TABLE appointments ADD CONSTRAINT chk_min_fee CHECK (fee >= 100);

Output

Query OK, 1 row affected
ERROR 3819 (HY000): Check constraint 'chk_min_fee' is violated.

add-works.sql

UPDATE appointments SET fee = 100 WHERE fee < 100;
ALTER TABLE appointments ADD CONSTRAINT chk_min_fee CHECK (fee >= 100);

Output

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Constraint	Add	Drop
PRIMARY KEY	`ADD PRIMARY KEY (col)`	`DROP PRIMARY KEY`
UNIQUE	`ADD CONSTRAINT uq UNIQUE (col)`	`DROP INDEX uq`
FOREIGN KEY	`ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES t(col)`	`DROP FOREIGN KEY fk`
CHECK	`ADD CONSTRAINT chk CHECK (expr)`	`DROP CHECK chk` (or `DROP CONSTRAINT chk`)
NOT NULL	`MODIFY col type NOT NULL`	`MODIFY col type NULL`
DEFAULT	`ALTER COLUMN col SET DEFAULT v`	`ALTER COLUMN col DROP DEFAULT`

query.sql

ALTER TABLE appointments DROP CONSTRAINT chk_min_fee;

Output

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

7. Constraints vs Validation in Your Application

Java applications usually validate input too (for example with Bean Validation's @NotNull and @Size). That's good for friendly messages, but it doesn't replace database constraints: other programs, scripts and bugs can bypass application code. Use both: validation for the user, constraints as the last line of defence.

8. Practice Exercises

Exercise 1: Add a rule that appointment dates must be in 2026 or later.

solution.sql

ALTER TABLE appointments ADD CONSTRAINT chk_year CHECK (appt_date >= '2026-01-01');
INSERT INTO appointments (patient, doctor_id, appt_date) VALUES ('Dev', 1, '2025-12-31');

Output

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0
ERROR 3819 (HY000): Check constraint 'chk_year' is violated.

Exercise 2: List all constraints in the hospital database with their table and type.

solution.sql

SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'hospital'
ORDER BY table_name, constraint_type;

Result: 8 rows

TABLE_NAME	CONSTRAINT_NAME	CONSTRAINT_TYPE
appointments	chk_fee	CHECK
appointments	chk_status	CHECK
appointments	chk_year	CHECK
appointments	fk_appt_doctor	FOREIGN KEY
appointments	PRIMARY	PRIMARY KEY
appointments	uq_slot	UNIQUE
doctors	PRIMARY	PRIMARY KEY
doctors	reg_no	UNIQUE

9. Summary

Constraints are rules MySQL enforces on every write: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.
Define them at column level or table level; name them for clear errors.
Add or drop them later with ALTER TABLE; existing data must already comply.
List them in information_schema.table_constraints and check_constraints.
Keep application validation too, but let the database have the final say.

Was this tutorial helpful?

Help Us Improve

MySQL Constraints

1. Why Use Constraints?

2. The Six Constraints in MySQL

3. All Constraints in One Example

4. Column-Level vs Table-Level Syntax

5. Listing a Table's Constraints

6. Adding and Dropping Constraints Later

7. Constraints vs Validation in Your Application

8. Practice Exercises

9. Summary