A constraint is a rule the server refuses to break. That is the whole argument for using them: application code can be bypassed by a migration script, a second application, an admin panel or somebody at a terminal, and a constraint cannot.
A primary key uniquely identifies a row, cannot be NULL, and in InnoDB determines the physical order the rows are stored in. A short, monotonically increasing key - a surrogate INT AUTO_INCREMENT - is therefore the sensible default, because inserts append rather than landing in the middle. A random UUID as the primary key is a known performance problem for exactly this reason; if you need one, store it in a separate unique column.
A foreign key says a value must exist in another table. It also decides what happens when the parent goes: ON DELETE RESTRICT refuses - the safe default; ON DELETE CASCADE deletes the children, right for genuinely owned rows like order lines, dangerous elsewhere; ON DELETE SET NULL keeps the child and forgets the parent.
UNIQUE prevents duplicates and permits multiple NULLs, because unknowns are not known to be equal. NOT NULL requires a value. DEFAULT supplies one. CHECK enforces a condition and is genuinely enforced from MySQL 8.0.16 - before that it was parsed and ignored, which is worth knowing when reading an old schema.
CREATE TABLE subscriptions (
-- Short and increasing: InnoDB stores rows in primary key order, so
-- inserts append instead of landing in the middle of the tree.
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
plan ENUM('free','pro','team') NOT NULL DEFAULT 'free',
seats SMALLINT UNSIGNED NOT NULL DEFAULT 1,
started_on DATE NOT NULL,
ends_on DATE NULL,
external_ref CHAR(36) NULL,
-- One active subscription per customer. NULLs are allowed more than
-- once, because unknown is not known to equal unknown.
UNIQUE KEY uq_subscription_ref (external_ref),
-- Genuinely enforced from 8.0.16. Before that, parsed and ignored.
CONSTRAINT chk_seats CHECK (seats BETWEEN 1 AND 500),
CONSTRAINT chk_date_order CHECK (ends_on IS NULL OR ends_on > started_on),
CONSTRAINT fk_sub_customer
FOREIGN KEY (customer_id) REFERENCES customers (id)
ON DELETE CASCADE -- a subscription cannot outlive its customer
ON UPDATE CASCADE
) ENGINE = InnoDB;
-- Each of these is now refused by the server, not by your application:
-- INSERT INTO subscriptions (customer_id, seats, started_on)
-- VALUES (999999, 1, '2026-01-01'); -- no such customer
-- INSERT INTO subscriptions (customer_id, seats, started_on)
-- VALUES (1, 0, '2026-01-01'); -- seats out of range
-- INSERT INTO subscriptions (customer_id, started_on, ends_on)
-- VALUES (1, '2026-06-01', '2026-01-01'); -- ends before it starts