MySQL Constraints Overview

Constraints -- NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT -- are rules attached to table columns that stop invalid data from ever being stored.

In this page:

What are Constraints?
NOT NULL and UNIQUE
PRIMARY KEY and FOREIGN KEY
CHECK and DEFAULT
Adding Constraints to an Existing Table

Syntax

sql

CREATE TABLE table_name (
  column_name datatype constraint,
  column_name datatype constraint
);

What are Constraints?

Constraints are rules attached to a table's columns that restrict what data can ever be stored there, catching invalid data at the moment it's inserted or updated rather than allowing bad data to slip into the database.

Example: What are Constraints?

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) NOT NULL UNIQUE
);

NOT NULL and UNIQUE

NOT NULL requires every row to have a value in that column, rejecting any insert or update that would leave it empty, while UNIQUE ensures no two rows in the table can ever share the same value in that column.

Example: NOT NULL and UNIQUE

sql

CREATE TABLE users (
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE
);

PRIMARY KEY and FOREIGN KEY

PRIMARY KEY marks a column as the unique identifier for each row, automatically combining NOT NULL and UNIQUE, while FOREIGN KEY links a column to a primary key in another table, ensuring referenced rows actually exist.

Example: PRIMARY KEY and FOREIGN KEY

sql

CREATE TABLE authors (id INT PRIMARY KEY);
CREATE TABLE books (
  id INT PRIMARY KEY,
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

CHECK and DEFAULT

CHECK enforces a custom condition that every value in a column must satisfy, such as a price that can never be negative, while DEFAULT supplies an automatic fallback value whenever an insert doesn't explicitly provide one.

Example: CHECK and DEFAULT

sql

CREATE TABLE products (
  price DECIMAL(10,2) CHECK (price >= 0),
  status VARCHAR(20) DEFAULT 'active'
);

Adding Constraints to an Existing Table

Constraints don't have to be defined only when a table is first created -- ALTER TABLE can add a new constraint like UNIQUE, CHECK, or FOREIGN KEY to a table that already exists and already contains data.

Example: Adding Constraints to an Existing Table

sql

ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price >= 0);

Chapter Quiz — Complete all 8 topics to unlock

0/8 topics done

Complete these topics first:

← Previous Lesson JSON Data Type Next Lesson → NOT NULL Constraint