Constraints — MySQL

MySQL Constraints

Constraints are rules enforced at the database level that guarantee the accuracy and consistency of data. They catch invalid data before it ever gets stored, protecting your application from the class of bugs where "impossible" data somehow ends up in the database. MySQL supports six main constraint types: NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, and FOREIGN KEY.

Constraint Types Overview

Constraint	Purpose	Notes
NOT NULL	Disallow NULL values	Applied per column; default is nullable
UNIQUE	Enforce distinct values	NULLs are allowed and not compared to each other
CHECK	Custom boolean validation	MySQL 8.0.16+ fully enforced; earlier versions parsed but ignored
DEFAULT	Provide fallback value	Literal constant or expression (expressions: 8.0.13+)
PRIMARY KEY	Unique non-null row identifier	One per table; creates the clustered index in InnoDB
FOREIGN KEY	Referential integrity	InnoDB only; automatically creates an index on FK column

NOT NULL — NULL vs Empty String

The NOT NULL constraint prevents a column from storing a NULL value. NULL means "unknown" or "not applicable" — it is not zero, not an empty string, and not false.

This distinction matters: an empty string '' is a valid value that passes NOT NULL. If you want to reject both NULL and empty strings, add a CHECK constraint.

SQL

CREATE TABLE users (
  id         INT          NOT NULL AUTO_INCREMENT,
  email      VARCHAR(255) NOT NULL,  -- required
  name       VARCHAR(100) NOT NULL,  -- required
  bio        TEXT,                   -- optional (nullable by default)
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- Fails: email is NOT NULL and no default
INSERT INTO users (name) VALUES ('Alice');
-- ERROR 1364: Field 'email' doesn't have a default value

-- Succeeds: both required columns provided
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

-- Also succeeds (NOT NULL allows empty string)
INSERT INTO users (email, name) VALUES ('', 'Bob');  -- empty email passes NOT NULL!

SQL

-- Reject both NULL and empty strings with a CHECK constraint
ALTER TABLE users
  ADD CONSTRAINT chk_email_nonempty CHECK (email != '');

Tip

Make columns NOT NULL by default. Only allow NULL when the absence of a value is genuinely meaningful — for example, an optional middle name or a field populated by a later process.

UNIQUE — Multiple NULLs Are Allowed

The UNIQUE constraint ensures all values in a column (or combination of columns) are distinct across all rows. Unlike PRIMARY KEY, a UNIQUE column can contain NULL values — and importantly, multiple NULLs are allowed because NULL is not equal to NULL in SQL semantics.

SQL

-- Single-column UNIQUE (inline syntax)
CREATE TABLE users (
  id    INT          NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

-- Named UNIQUE constraint (preferred — readable errors, easier ALTER)
CREATE TABLE users (
  id    INT          NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT uq_user_email UNIQUE (email)
);

-- Composite UNIQUE: the combination must be unique
CREATE TABLE memberships (
  user_id    INT NOT NULL,
  org_id     INT NOT NULL,
  joined_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_org (user_id, org_id)
);

-- Multiple NULLs in a UNIQUE nullable column are allowed
CREATE TABLE employees (
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  badge VARCHAR(20) UNIQUE   -- nullable UNIQUE: multiple NULLs allowed
);
INSERT INTO employees (badge) VALUES (NULL);
INSERT INTO employees (badge) VALUES (NULL);  -- succeeds! NULLs not compared

CHECK Constraint (MySQL 8.0.16+)

The CHECK constraint allows you to define a custom boolean expression that every row must satisfy. Before MySQL 8.0.16, CHECK clauses were parsed but silently ignored. From 8.0.16 onwards they are fully enforced.

CHECK constraints can reference multiple columns in the same row, allowing cross-column validation.

SQL

CREATE TABLE products (
  id       INT            NOT NULL AUTO_INCREMENT,
  name     VARCHAR(255)   NOT NULL,
  price    DECIMAL(10, 2) NOT NULL,
  discount DECIMAL(5, 2)  NOT NULL DEFAULT 0,
  stock    INT            NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  CONSTRAINT chk_price_positive    CHECK (price > 0),
  CONSTRAINT chk_discount_range    CHECK (discount >= 0 AND discount <= 100),
  CONSTRAINT chk_stock_nonnegative CHECK (stock >= 0),
  -- Cross-column: final price after discount must be positive
  CONSTRAINT chk_net_price         CHECK (price * (1 - discount/100) > 0)
);

-- Violates chk_price_positive
INSERT INTO products (name, price) VALUES ('Widget', -5.00);
-- ERROR 3819: Check constraint 'chk_price_positive' is violated.

-- Cross-column CHECK: end date must not precede start date
CREATE TABLE events (
  id         INT      NOT NULL AUTO_INCREMENT,
  title      VARCHAR(255) NOT NULL,
  start_date DATE     NOT NULL,
  end_date   DATE     NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

Note

CHECK constraints are evaluated on every INSERT and UPDATE. They cannot reference other tables, call stored functions, or use non-deterministic functions like NOW() or RAND().

CHECK Constraint Examples

SQL

-- Enum-like CHECK (alternative to ENUM type)
CREATE TABLE orders (
  id     INT         NOT NULL AUTO_INCREMENT,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  PRIMARY KEY (id),
  CONSTRAINT chk_order_status
    CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled'))
);

-- Age range (nullable column)
CREATE TABLE employees (
  id   INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  age  INT,
  PRIMARY KEY (id),
  CONSTRAINT chk_age CHECK (age IS NULL OR (age >= 16 AND age <= 120))
);

-- Percentage rate stored as decimal (0.00 to 1.00)
CREATE TABLE commissions (
  id   INT            NOT NULL AUTO_INCREMENT,
  rate DECIMAL(5, 4)  NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT chk_rate CHECK (rate >= 0 AND rate <= 1)
);

DEFAULT Values

The DEFAULT constraint specifies a value to use when a column is omitted from an INSERT statement. MySQL 8.0.13+ allows expression defaults (like UUID() or (col1 + col2)); earlier versions only support literal constants and a few special values like CURRENT_TIMESTAMP.

SQL

CREATE TABLE articles (
  id          INT          NOT NULL AUTO_INCREMENT,
  title       VARCHAR(255) NOT NULL,
  status      VARCHAR(20)  NOT NULL DEFAULT 'draft',         -- literal default
  view_count  INT          NOT NULL DEFAULT 0,               -- numeric default
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                                    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- MySQL 8.0.13+: expression defaults
CREATE TABLE logs (
  id      INT      NOT NULL AUTO_INCREMENT,
  uuid    CHAR(36) NOT NULL DEFAULT (UUID()),                -- function call
  score   INT      NOT NULL DEFAULT (FLOOR(RAND() * 100)),  -- expression
  message TEXT,
  PRIMARY KEY (id)
);

-- Using DEFAULT keyword explicitly in INSERT
INSERT INTO articles (title, status) VALUES ('Hello', DEFAULT);

Naming Constraints

Always name your constraints with CONSTRAINT constraint_name. Named constraints:

Produce clearer error messages (you see the constraint name, not just a generic error code).
Are required when you need to drop a specific constraint with ALTER TABLE.
Make the schema self-documenting and searchable in information_schema.

SQL

CREATE TABLE order_items (
  id         INT            NOT NULL AUTO_INCREMENT,
  order_id   INT            NOT NULL,
  product_id INT            NOT NULL,
  quantity   INT            NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT uq_order_product UNIQUE      (order_id, product_id),
  CONSTRAINT chk_quantity     CHECK       (quantity > 0),
  CONSTRAINT chk_unit_price   CHECK       (unit_price > 0),
  CONSTRAINT fk_item_order    FOREIGN KEY (order_id)
    REFERENCES orders   (id) ON DELETE CASCADE,
  CONSTRAINT fk_item_product  FOREIGN KEY (product_id)
    REFERENCES products (id) ON DELETE RESTRICT
);

Adding and Dropping Constraints

SQL

-- Add a NOT NULL constraint (requires MODIFY to redefine column)
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;

-- Add a named UNIQUE constraint
ALTER TABLE users ADD CONSTRAINT uq_user_phone UNIQUE (phone);

-- Drop a UNIQUE constraint (uses index name)
ALTER TABLE users DROP INDEX uq_user_phone;

-- Add a CHECK constraint (MySQL 8.0.16+)
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);

-- Drop a CHECK constraint (by name)
ALTER TABLE products DROP CHECK chk_price;

-- Add a FOREIGN KEY
ALTER TABLE products
  ADD CONSTRAINT fk_product_category
  FOREIGN KEY (category_id) REFERENCES categories (id);

-- Drop a FOREIGN KEY
ALTER TABLE products DROP FOREIGN KEY fk_product_category;

-- Audit constraints on a table
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products';

Constraint Violations — Error Codes

When a constraint is violated, MySQL raises an error and rolls back the offending statement. Knowing the error codes helps you handle violations appropriately in application code.

Constraint	Error Code	SQL State	Message
NOT NULL	1048	23000	Column 'col' cannot be null
UNIQUE / PRIMARY KEY	1062	23000	Duplicate entry for key
FOREIGN KEY (insert/update)	1452	23000	Cannot add or update a child row
FOREIGN KEY (delete/update parent)	1451	23000	Cannot delete or update a parent row
CHECK	3819	HY000	Check constraint 'name' is violated

SQL

-- Application-side: skip duplicate key errors silently
INSERT IGNORE INTO users (email, name) VALUES ('alice@example.com', 'Alice');

-- Use ON DUPLICATE KEY UPDATE to upsert
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice Updated')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Handle violations in stored procedures
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
BEGIN
  SET @error_msg = 'Constraint violation detected';
END;

Disabling Constraint Checks (Risks)

MySQL allows temporarily disabling foreign key checks, which is sometimes needed for bulk data loads or circular FK references. Use this with extreme care — it bypasses referential integrity and can silently introduce orphaned rows.

SQL

-- Disable FK checks for bulk import (restore immediately after!)
SET foreign_key_checks = 0;

-- ... bulk INSERT or LOAD DATA ...

SET foreign_key_checks = 1;

-- After re-enabling, manually verify referential integrity
SELECT COUNT(*) FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;  -- orphaned order_items

Warning

Disabling foreign_key_checks silently allows orphaned rows to be inserted. Always re-enable it immediately after the operation and verify data integrity before proceeding.

Constraint Audit Query

SQL

-- Full constraint audit for a schema
SELECT
  tc.TABLE_NAME,
  tc.CONSTRAINT_NAME,
  tc.CONSTRAINT_TYPE,
  GROUP_CONCAT(kcu.COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION) AS columns
FROM information_schema.TABLE_CONSTRAINTS tc
JOIN information_schema.KEY_COLUMN_USAGE kcu
  ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
  AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
  AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE tc.TABLE_SCHEMA = 'myapp'
GROUP BY tc.TABLE_NAME, tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE
ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_TYPE;