Constraints in MySQL

Constraints are rules the database enforces so invalid data can never be stored — no matter which application writes it.

Constraint	Guarantees
NOT NULL	The column always has a value
UNIQUE	No two rows share the value
PRIMARY KEY	Unique and not null; one per table
FOREIGN KEY	The value exists in the referenced table
CHECK	A custom condition holds (MySQL 8.0.16+)
DEFAULT	A fallback value when none is supplied
AUTO_INCREMENT	The server generates sequential numbers

Declaring constraints

CREATE TABLE courses (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code      VARCHAR(10)  NOT NULL UNIQUE,
  title     VARCHAR(150) NOT NULL,
  credits   TINYINT      NOT NULL DEFAULT 3,
  seats     SMALLINT     NOT NULL DEFAULT 40,
  dept_code VARCHAR(10)  NOT NULL,

  CONSTRAINT chk_credits CHECK (credits BETWEEN 1 AND 6),
  CONSTRAINT chk_seats   CHECK (seats > 0),
  CONSTRAINT uq_dept_title UNIQUE (dept_code, title),
  CONSTRAINT fk_course_dept FOREIGN KEY (dept_code)
    REFERENCES departments(code)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

Naming constraints (chk_credits, fk_course_dept) makes error messages readable and lets you drop them later by name.

Adding and removing constraints

ALTER TABLE courses ADD CONSTRAINT chk_title CHECK (CHAR_LENGTH(title) >= 3);
ALTER TABLE courses DROP CHECK chk_title;

ALTER TABLE courses ADD UNIQUE uq_code (code);
ALTER TABLE courses DROP INDEX uq_code;

ALTER TABLE courses MODIFY title VARCHAR(150) NULL;   -- remove NOT NULL

Composite UNIQUE

A multi-column UNIQUE allows each value individually but forbids the combination:

-- a student can enroll in a course only once per semester
ALTER TABLE enrollments
ADD CONSTRAINT uq_enroll UNIQUE (student_id, course_id, semester);

CHECK constraint notes

• Enforced from MySQL 8.0.16; earlier versions parsed and ignored them.
• Cannot reference other tables, subqueries, or non-deterministic functions like NOW().
• A CHECK passes when it evaluates to TRUE or UNKNOWN, so a NULL column value does not fail the check. Combine with NOT NULL when you need both.

Inspecting constraints

SHOW CREATE TABLE courses\G

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = DATABASE() AND table_name = 'courses';

Why constraints beat application validation

Application code changes, teams change, and data gets imported by scripts and admins who bypass your validation layer. A constraint is the last line of defence and the only one that is always enforced. Validate in the app for good error messages, and in the database for correctness.