Constraints and Keys

Introduction

Constraints enforce rules on your data—unique emails, required fields, valid relationships between tables. This chapter deepens PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, and CHECK (MySQL 8.0.16+), including ON DELETE and ON UPDATE behaviors for referential integrity.

Prerequisites

Creating Tables and Data Types
Inserting Data
Database blog_dev with users and posts

PRIMARY KEY and AUTO_INCREMENT

Every table should have a primary key—usually surrogate id:

sql

CREATE TABLE demo_pk (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  note VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

Code explanation:

PRIMARY KEY implies NOT NULL and UNIQUE
InnoDB stores rows clustered by primary key—choose stable, narrow keys

Composite primary key (junction tables):

sql

CREATE TABLE post_tags (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id)
) ENGINE=InnoDB;

UNIQUE, NOT NULL, DEFAULT

sql

CREATE TABLE subscribers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  plan VARCHAR(20) NOT NULL DEFAULT 'free',
  subscribed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_subscribers_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Constraint	Effect
NOT NULL	Column must have value on insert
UNIQUE	No duplicate values (NULL allowed once per column in MySQL)
DEFAULT	Used when insert omits column

CHECK Constraints (MySQL 8.0.16+)

sql

CREATE TABLE products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT chk_price_positive CHECK (price >= 0),
  CONSTRAINT chk_stock_non_negative CHECK (stock >= 0)
) ENGINE=InnoDB;

Invalid insert fails:

sql

INSERT INTO products (name, price, stock) VALUES ('Bad', -1, 10);

Add check to existing table:

sql

ALTER TABLE products
  ADD CONSTRAINT chk_name_not_empty CHECK (CHAR_LENGTH(name) > 0);

FOREIGN KEY Basics

Link posts.user_id to users.id:

sql

-- Ensure posts.user_id type matches users.id
ALTER TABLE posts
  ADD CONSTRAINT fk_posts_user
  FOREIGN KEY (user_id) REFERENCES users (id);

Code explanation:

Child column user_id must match parent type and index
Parent users.id must be PRIMARY KEY or UNIQUE

Insert valid row:

sql

INSERT INTO posts (user_id, title, body)
VALUES (1, 'FK demo', 'Valid user reference.');

Insert invalid user_id fails:

sql

INSERT INTO posts (user_id, title, body)
VALUES (99999, 'Orphan', 'No such user.');

ON DELETE and ON UPDATE Actions

Action	ON DELETE behavior
RESTRICT / NO ACTION	Block delete if children exist (default)
CASCADE	Delete child rows when parent deleted
SET NULL	Set FK column NULL (column must allow NULL)

Example cascade on comments table:

sql

CREATE TABLE comments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  body TEXT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_comments_post
    FOREIGN KEY (post_id) REFERENCES posts (id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

Delete post removes its comments automatically.

SET NULL on optional FK:

sql

-- Hypothetical optional assignee
-- FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL

ON UPDATE CASCADE—when parent PK changes, update child FKs (rare with auto-increment ids).

Drop and Inspect Constraints

sql

SHOW CREATE TABLE posts\G
 
SELECT
  CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'blog_dev'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Remove foreign key:

sql

ALTER TABLE posts DROP FOREIGN KEY fk_posts_user;

Design Guidelines

Practice	Reason
Index all FK columns	Faster joins and cascade checks
Prefer `BIGINT` ids for growth	Avoid overflow
Use CASCADE deliberately	Accidental mass delete risk
App + FK together	ORM does not replace database integrity

Tip

Start Without FK on Rapid Prototypes

Early spikes may skip FKs; add before production to catch orphan rows.

Contrast With Document Databases

In MongoDB, relationships are modeled with embedded subdocuments or ObjectId references instead of FOREIGN KEY—see Embedded vs Referenced Modeling. MySQL ON DELETE CASCADE has no single-document equivalent; document DBs handle orphans in application code or $lookup reports.

Relational (this chapter)	Document (MongoDB)
`FOREIGN KEY`	Reference field or embed
`UNIQUE` index	Unique index on field
`CHECK`	JSON Schema `validator` — MongoDB validation

FAQ

FK slow on bulk import?

Temporarily disable FOREIGN_KEY_CHECKS=0 for trusted loads only—re-enable after.

Can FK reference non-PK?

Must reference PRIMARY KEY or UNIQUE column.

Multiple FKs to same table?

Yes—e.g. author_id, editor_id both → users.

Charset mismatch FK error?

Parent/child tables should share compatible charset/collation.

Replace PRIMARY KEY?

Painful with FKs—plan keys early.

CHECK ignored on old MySQL?

CHECK parsed but ignored before 8.0.16—verify version.