MySQL Constraints Overview
In this page:
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?
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
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
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
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
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: