MySQL Constraints

Why Constraints Matter

Without constraints, a table will happily accept NULLs where a value is required, duplicate emails, negative ages, or prices with no floor. Constraints move that validation logic into the database itself, so every application and every developer touching the data is protected by the same rules, not just the ones remembered to check in code.

NOT NULL

A NOT NULL constraint requires a column to always contain a value; attempting to insert or update a row with NULL in that column raises an error instead of silently storing a missing value.

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(120) NOT NULL
);

UNIQUE

A UNIQUE constraint guarantees that no two rows can share the same value in that column (or combination of columns, for a composite unique constraint). Unlike a primary key, a table can have several UNIQUE constraints, and a UNIQUE column may still allow one NULL value in MySQL.

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(120) NOT NULL UNIQUE
);

DEFAULT

DEFAULT does not reject bad data directly, but it supplies a sensible fallback value when a column is left out of an INSERT statement, which keeps columns like status flags or timestamps consistently populated.

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

CHECK

A CHECK constraint defines a boolean expression that every row must satisfy. MySQL 8.0.16 and later enforces CHECK constraints at write time; on earlier versions the syntax is accepted but silently ignored, so verify your server version before relying on it.

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  price DECIMAL(8,2) NOT NULL,
  CHECK (price >= 0)
);

• NOT NULL guards against missing values.
• UNIQUE guards against duplicate values.
• DEFAULT fills in a value when none is supplied.
• CHECK validates a custom condition on the value itself.