Skip to content
elephantoo

Constraints: keys, UNIQUE, CHECK & DEFAULT

Lesson 11 of 31 16 min read

PRIMARY KEY, FOREIGN KEY with ON DELETE/UPDATE, UNIQUE, NOT NULL, CHECK and DEFAULT.

A database's most valuable feature isn't storing data. It's refusing bad data. Constraints are rules attached to tables that MySQL enforces on every INSERT and UPDATE, no matter which application, script or person makes the change. Bugs in your code can't sneak invalid data past them.

ConstraintGuarantees
NOT NULLthe column always has a value
DEFAULTa value is filled in when none is given
PRIMARY KEYeach row is uniquely identifiable (unique + not null)
UNIQUEno duplicates in the column(s)
FOREIGN KEYvalues exist in another table
CHECKvalues satisfy a condition (MySQL 8.0.16+)

NOT NULL and DEFAULT#

SQL
CREATE TABLE accounts (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(30) NOT NULL,
    country     CHAR(2)     NOT NULL DEFAULT 'IN',
    plan        VARCHAR(10) NOT NULL DEFAULT 'free',
    created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    nickname    VARCHAR(30)
);

INSERT INTO accounts (username) VALUES ('asha');
INSERT INTO accounts (username, country) VALUES (NULL, 'GB');
SELECT id, username, country, plan, nickname FROM accounts;
Output
ERROR 1048 (23000): Column 'username' cannot be null
+----+----------+---------+------+----------+
| id | username | country | plan | nickname |
+----+----------+---------+------+----------+
|  1 | asha     | IN      | free | NULL     |
+----+----------+---------+------+----------+
  • Make columns NOT NULL unless "unknown" is a real, meaningful state. That one habit removes a whole class of NULL bugs.
  • MySQL 8.0.13+ allows expression defaults in brackets, e.g. DEFAULT (UUID()) or DEFAULT (CURRENT_DATE + INTERVAL 30 DAY).
  • ON UPDATE CURRENT_TIMESTAMP refreshes updated_at automatically whenever the row changes.

PRIMARY KEY#

Every table should have a primary key. InnoDB physically stores rows in primary-key order (the clustered index), so the key is also the fastest way to find a row.

SQL
CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,         -- natural key
    name VARCHAR(60) NOT NULL
);

CREATE TABLE enrolments (             -- composite key: one row per student per course
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_on DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

Surrogate keys (id INT AUTO_INCREMENT) never change and stay small. Natural keys (a country code, an ISBN) carry meaning but can change or turn out not to be unique after all. Most tables use a surrogate key, plus UNIQUE constraints on the natural identifiers.

UNIQUE#

SQL
CREATE TABLE members (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    team  VARCHAR(20)  NOT NULL,
    jersey TINYINT UNSIGNED,
    CONSTRAINT uq_members_email UNIQUE (email),
    CONSTRAINT uq_team_jersey UNIQUE (team, jersey)
);

INSERT INTO members (email, team, jersey) VALUES
    ('a@x.com', 'red', 7), ('b@x.com', 'blue', 7), ('c@x.com', 'red', NULL), ('d@x.com', 'red', NULL);
INSERT INTO members (email, team, jersey) VALUES ('e@x.com', 'red', 7);
Output
ERROR 1062 (23000): Duplicate entry 'red-7' for key 'members.uq_team_jersey'
  • Naming constraints (CONSTRAINT uq_members_email ...) gives readable error messages and makes them easy to drop later.
  • UNIQUE (team, jersey) is a composite unique key: number 7 can appear once per team.
  • Multiple NULLs are allowed in a UNIQUE column, which is why two red players without a jersey number were fine.

FOREIGN KEY: relationships you can trust#

A foreign key says "this column's value must exist in that table's key". It needs InnoDB, matching column types, and an index on both sides (MySQL creates the child-side index automatically if it's missing).

SQL
CREATE TABLE authors (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL
);

CREATE TABLE posts (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    title     VARCHAR(100) NOT NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES authors (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

INSERT INTO authors (name) VALUES ('Asha'), ('Rohan');
INSERT INTO posts (author_id, title) VALUES (1, 'Hello SQL'), (1, 'Indexes 101');
INSERT INTO posts (author_id, title) VALUES (99, 'Ghost post');
Output
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop`.`posts`, CONSTRAINT `fk_posts_author` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE)

Author 99 doesn't exist, so the insert is rejected. Deleting an author who still has posts is rejected too:

SQL
DELETE FROM authors WHERE id = 1;
Output
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop`.`posts`, CONSTRAINT `fk_posts_author` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE)

Referential actions

What should happen to children when the parent is deleted or its key changes?

ActionOn parent delete/update
RESTRICT / NO ACTION (default)reject the change while children exist
CASCADEdelete / update the children too
SET NULLset the child column to NULL (it must be nullable)

Choose deliberately. CASCADE suits things that make no sense without their parent (an order's line items). RESTRICT protects important history (you shouldn't delete a customer who has invoices). SET NULL suits optional links (a post whose editor left the company).

SQL
CREATE TABLE comments (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    body    VARCHAR(200) NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE
);
INSERT INTO comments (post_id, body) VALUES (1, 'Great!'), (1, 'Thanks'), (2, 'Useful');
DELETE FROM posts WHERE id = 1;
SELECT * FROM comments;
Output
+----+---------+--------+
| id | post_id | body   |
+----+---------+--------+
|  3 |       2 | Useful |
+----+---------+--------+

Deleting post 1 removed its two comments automatically.

Bulk imports sometimes load tables out of order. SET FOREIGN_KEY_CHECKS = 0; turns checking off for your session, but rows loaded meanwhile are not re-checked when you turn it back on. Use it only with data you trust, and always set it back to 1.

CHECK constraints#

SQL
CREATE TABLE products (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(60) NOT NULL,
    price  DECIMAL(8,2) NOT NULL,
    sale_price DECIMAL(8,2),
    stock  INT NOT NULL DEFAULT 0,
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_stock CHECK (stock >= 0),
    CONSTRAINT chk_sale CHECK (sale_price IS NULL OR sale_price < price)
);

INSERT INTO products (name, price, sale_price) VALUES ('Mouse', 20.00, 15.00);
INSERT INTO products (name, price, sale_price) VALUES ('Cable', 10.00, 12.00);
Output
ERROR 3819 (HY000): Check constraint 'chk_sale' is violated.

A CHECK can compare several columns of the same row, but it can't use subqueries, other tables or non-deterministic functions like NOW(). A condition that evaluates to NULL passes, so combine CHECK with NOT NULL where needed. Remember: on MySQL before 8.0.16 (and some MariaDB versions), check clauses may be parsed but not enforced.

Adding and removing constraints later#

SQL
ALTER TABLE accounts ADD CONSTRAINT uq_accounts_username UNIQUE (username);
ALTER TABLE accounts ADD CONSTRAINT chk_plan CHECK (plan IN ('free', 'pro', 'team'));
ALTER TABLE accounts DROP CHECK chk_plan;
ALTER TABLE accounts DROP INDEX uq_accounts_username;   -- UNIQUE constraints are indexes
ALTER TABLE posts DROP FOREIGN KEY fk_posts_author;

Adding a constraint to a table that already holds violating rows fails, so clean the data first. To list a table's constraints:

SQL
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products';
Output
+--------------------+-----------------+
| CONSTRAINT_NAME    | CONSTRAINT_TYPE |
+--------------------+-----------------+
| PRIMARY            | PRIMARY KEY     |
| chk_price_positive | CHECK           |
| chk_sale           | CHECK           |
| chk_stock          | CHECK           |
+--------------------+-----------------+

Common mistakes#

  • Leaving integrity to the application only. Sooner or later a script, an admin or a second app writes to the same tables. Constraints protect everyone.
  • Mismatched foreign-key types. INT vs INT UNSIGNED, or different character sets, give error 3780 ("incompatible").
  • CASCADE everywhere. One careless DELETE can wipe out large amounts of related data. Cascade only true "part-of" relationships.
  • Nullable columns by default. Start with NOT NULL and relax it only when you must.

What's next#

With trustworthy related tables in place, it's time for the most important multi-table skill in SQL: JOINs.

Check your understanding

Quick quiz

0/3 answered

  1. 1.A foreign key is declared ON DELETE CASCADE. What happens when you delete a parent row?

  2. 2.Since which MySQL version are CHECK constraints actually enforced?

  3. 3.How many NULLs can a UNIQUE column contain in MySQL?

Finished reading?

Mark this lesson complete to track your progress.