Skip to content

Table Constraints

Enforce data integrity with UNIQUE, NOT NULL, DEFAULT, and FOREIGN KEY.

Without constraints, anyone can insert invalid data: duplicate emails, missing names, or enrollments that reference students who do not exist. Constraints are rules the database enforces automatically. They prevent bad data from entering in the first place.

Definition

Table constraints enforce data integrity rules at the database level. UNIQUE ensures all values in a column are different, NOT NULL prevents missing values, DEFAULT provides a fallback value, and FOREIGN KEY links to another table's primary key.

Required step: If you do not have the school database and students table yet, run this setup in the playground:

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

INSERT INTO students (name, email)
VALUES
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]'),
  ('Charlie', '[email protected]');

Troubleshooting: If you get "table doesn't exist" errors, re-run the setup above. It uses IF NOT EXISTS so it is safe to run multiple times.

NOT NULL and UNIQUE

NOT NULL prevents missing values. UNIQUE prevents duplicates.

NOT NULL: email is required

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL
);

NOT NULL means the column must have a value on INSERT. Inserting a row without email fails.

UNIQUE: no duplicate emails

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

UNIQUE ensures all values in the column are different. Inserting two students with the same email fails. Note: UNIQUE allows multiple NULL values.

DEFAULT

DEFAULT provides a fallback value when INSERT omits the column.

Default values

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

If you INSERT without specifying status, it defaults to 'pending'. created_at defaults to the current time.

FOREIGN KEY

FOREIGN KEY ensures child rows reference valid parent rows. It enforces referential integrity.

Foreign key with ON DELETE CASCADE

CREATE TABLE enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

ON DELETE CASCADE means: when a student is deleted, their enrollments are deleted too. ON UPDATE CASCADE means: if the student id changes, enrollments update automatically.

Foreign key with ON DELETE RESTRICT

CREATE TABLE enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id)
    ON DELETE RESTRICT
);

ON DELETE RESTRICT means: you cannot delete a student who has enrollments. The database blocks the delete.

Key choices:ON DELETE Actions

  • CASCADE: delete or update child rows automatically
  • RESTRICT: block the parent delete/update if children exist
  • SET NULL: set the foreign key column to NULL in child rows
  • NO ACTION: same as RESTRICT in MySQL (both are evaluated immediately)

CHECK

CHECK constraints enforce custom validation rules on column values. MySQL 8.0.16+ enforces them; earlier versions parse but ignore them.

Custom rules:CHECK constraints

A CHECK constraint defines a condition that must be true for every row. If the condition evaluates to FALSE or NULL, the INSERT or UPDATE is rejected.

  • Can reference a single column (e.g., age >= 0)
  • Can reference multiple columns (e.g., end_date > start_date)
  • Named constraints make error messages clearer

CHECK with a single column

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT NOT NULL,
  CONSTRAINT chk_price CHECK (price > 0),
  CONSTRAINT chk_quantity CHECK (quantity >= 0)
);

INSERT INTO products (name, price, quantity) VALUES ('Widget', -5, 10) fails because price > 0 is violated. The constraint name 'chk_price' appears in the error message.

CHECK with multiple columns

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

This ensures end_date is never before start_date. It validates a relationship between two columns, which a simple NOT NULL or UNIQUE constraint cannot do.

Viewing Constraints

Show all constraints on a table

SHOW CREATE TABLE enrollments\G

SHOW CREATE TABLE displays the full CREATE TABLE statement, including all constraints, indexes, and storage engine. The \\G formats output vertically.

What does ON DELETE CASCADE do?

A UNIQUE column can contain multiple NULL values?

Key Takeaways

  • NOT NULL prevents missing values; UNIQUE prevents duplicates (multiple NULLs allowed).
  • DEFAULT provides automatic values when INSERT omits a column.
  • FOREIGN KEY ensures child rows reference valid parent rows.
  • ON DELETE CASCADE deletes child rows automatically; RESTRICT prevents parent deletion.
  • Parent and child tables must use InnoDB storage engine.
  • Use SHOW CREATE TABLE to inspect all constraints on a table.

Ready to test your knowledge?

Take a Quiz

Related Tutorials