PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, DEFAULT, and Generated Columns

Learning outcomes

Types answer “what kind of value can this column represent?” Constraints answer “which database states are allowed?” A robust schema makes impossible or contradictory states fail close to the data rather than hoping every application path performs identical validation.

This lesson uses InnoDB tables and MySQL 8.4 enforced constraints. You will inspect the resulting metadata and indexes instead of treating constraint syntax as decoration.

Primary keys define row identity—and InnoDB organization

A PRIMARY KEY is both a relational identity constraint and, for InnoDB, the clustered index that organizes row data. Primary-key columns are unique and non-null. A table has at most one primary key, but it can contain multiple columns.

DROP TABLE IF EXISTS servicehub_lab.work_order_items;DROP TABLE IF EXISTS servicehub_lab.work_orders;DROP TABLE IF EXISTS servicehub_lab.customers;CREATE TABLE servicehub_lab.customers (  customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  external_ref VARCHAR(40) NOT NULL,  display_name VARCHAR(120) NOT NULL,  PRIMARY KEY (customer_id),  CONSTRAINT uq_customers_external_ref UNIQUE (external_ref)) ENGINE=InnoDB;

The surrogate customer_id gives a narrow stable InnoDB clustered key; external_ref remains a separately enforced business key. A UNIQUE constraint is not “another primary key”; its business meaning is uniqueness, and MySQL exposes it through constraint/index metadata.

Foreign keys encode relationships and lifecycle rules

A foreign key says that a child key value must refer to an allowed parent key value (or be NULL if the child column permits it). The referential action defines what happens when the parent key is deleted or updated. Choose the action from domain lifecycle, not convenience.

CREATE TABLE servicehub_lab.work_orders (  work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  customer_id BIGINT UNSIGNED NOT NULL,  status VARCHAR(20) NOT NULL DEFAULT 'new',  estimated_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,  scheduled_at DATETIME NULL,  PRIMARY KEY (work_order_id),  KEY ix_work_orders_customer (customer_id),  CONSTRAINT fk_work_orders_customer    FOREIGN KEY (customer_id)    REFERENCES servicehub_lab.customers(customer_id)    ON UPDATE RESTRICT    ON DELETE RESTRICT,  CONSTRAINT chk_work_orders_cost CHECK (estimated_cost >= 0),  CONSTRAINT chk_work_orders_status    CHECK (status IN ('new','scheduled','in_progress','done','cancelled'))) ENGINE=InnoDB;

RESTRICT communicates that a customer cannot be removed while work orders still reference it. A system that needs anonymization, archival, or soft deletion should model that workflow explicitly instead of blindly changing the foreign key to CASCADE.

CHECK constraints enforce row predicates

MySQL 8.4 supports enforced CHECK constraints. For each row, the expression must evaluate to TRUE or UNKNOWN; FALSE violates the constraint. This means nullable columns require careful reasoning: a predicate involving NULL can evaluate to UNKNOWN, which is not the same as false.

INSERT INTO servicehub_lab.customers (external_ref, display_name)VALUES ('CUST-001', 'Northwind Field Services');INSERT INTO servicehub_lab.work_orders  (customer_id, status, estimated_cost)VALUES (1, 'new', 125.50);-- Deliberately invalid: negative cost.INSERT INTO servicehub_lab.work_orders  (customer_id, status, estimated_cost)VALUES (1, 'new', -1.00);SHOW WARNINGS;

The invalid write should fail with a check-constraint error. The important behavior is not the exact numeric error code; it is that the database rejects a state that violates an explicit invariant.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCEDFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSWHERE TABLE_SCHEMA='servicehub_lab'  AND TABLE_NAME='work_orders'ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;SELECT CONSTRAINT_NAME, CHECK_CLAUSEFROM INFORMATION_SCHEMA.CHECK_CONSTRAINTSWHERE CONSTRAINT_SCHEMA='servicehub_lab'ORDER BY CONSTRAINT_NAME;SELECT CONSTRAINT_NAME, COLUMN_NAME,       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE TABLE_SCHEMA='servicehub_lab'  AND TABLE_NAME='work_orders'ORDER BY CONSTRAINT_NAME, ORDINAL_POSITION;

Composite keys model compound identity

A work-order line is naturally identified by a work order plus its line number. This is a good example of table-level composite constraints. The column pair forms the identity; neither column alone is sufficient.

CREATE TABLE servicehub_lab.work_order_items (  work_order_id BIGINT UNSIGNED NOT NULL,  line_no SMALLINT UNSIGNED NOT NULL,  description VARCHAR(200) NOT NULL,  quantity DECIMAL(10,2) NOT NULL,  unit_price DECIMAL(12,2) NOT NULL,  line_total DECIMAL(14,2)    GENERATED ALWAYS AS (quantity * unit_price) STORED,  PRIMARY KEY (work_order_id, line_no),  CONSTRAINT fk_items_work_order    FOREIGN KEY (work_order_id)    REFERENCES servicehub_lab.work_orders(work_order_id)    ON DELETE CASCADE,  CONSTRAINT chk_items_quantity CHECK (quantity > 0),  CONSTRAINT chk_items_unit_price CHECK (unit_price >= 0)) ENGINE=InnoDB;

Here ON DELETE CASCADE can be defensible because line items have no independent lifecycle once their containing work order is deleted. Contrast that with customer deletion, where cascading through historical work orders may destroy records that should be retained. The same keyword can be safe in one relationship and disastrous in another.

Defaults and generated columns solve different problems

A default supplies a value when an insert omits the column. A generated column derives its value from an expression over the row. MySQL supports literal defaults and, with documented restrictions, expression defaults. Generated columns can be VIRTUAL or STORED: virtual values are computed when read, while stored values are materialized in the table and maintained when base columns change.

SHOW CREATE TABLE servicehub_lab.work_order_items;INSERT INTO servicehub_lab.work_order_items  (work_order_id, line_no, description, quantity, unit_price)VALUES (1, 1, 'Inspection', 2.00, 75.00);SELECT work_order_id, line_no, quantity, unit_price, line_totalFROM servicehub_lab.work_order_items;SELECT COLUMN_NAME, COLUMN_DEFAULT, EXTRA, GENERATION_EXPRESSIONFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA='servicehub_lab'  AND TABLE_NAME='work_order_items'ORDER BY ORDINAL_POSITION;

Expected line_total is 150.00. The application did not supply it. If an application tries to provide an arbitrary explicit value for a generated column, MySQL permits only DEFAULT in the insert context. This protects the derivation contract.

Failure drill: disabling a useful constraint to make an import pass

A dangerous migration habit is to weaken or remove constraints when data fails to load, without proving why the data violates the model. That transforms a visible data-quality failure into stored inconsistency.

CREATE TEMPORARY TABLE incoming_orders (  external_ref VARCHAR(40),  status VARCHAR(20),  estimated_cost DECIMAL(12,2));INSERT INTO incoming_orders VALUES  ('CUST-001','new',50.00),  ('MISSING','new',10.00),  ('CUST-001','impossible_status',20.00),  ('CUST-001','new',-5.00);SELECT i.*FROM incoming_orders AS iLEFT JOIN servicehub_lab.customers AS c  ON c.external_ref=i.external_refWHERE c.customer_id IS NULL   OR i.status NOT IN ('new','scheduled','in_progress','done','cancelled')   OR i.estimated_cost < 0;

Staging makes the failure explainable without weakening the target. Later ingestion chapters can automate this pattern at scale.

Hands-on lab: prove the schema rejects invalid states

1. Create the three constrained tables exactly as shown.
2. Capture SHOW CREATE TABLE for each.
3. Insert one valid customer, work order, and line item.
4. Attempt a duplicate external_ref, a missing parent customer, a negative cost, a bad status, and a zero quantity. Record each failure.
5. Query TABLE_CONSTRAINTS, KEY_COLUMN_USAGE, REFERENTIAL_CONSTRAINTS, CHECK_CONSTRAINTS, and STATISTICS.
6. Delete the work order and verify that its line items cascade while the customer remains.
7. Re-seed the clean records for Lesson 4.

Production judgment and references

Prefer declarative constraints for invariants the database can express cheaply and correctly across all clients. Application validation still matters for user experience and cross-service business rules, but it should not be the only barrier protecting durable relational state. Name important constraints deliberately so production errors and migration diffs are interpretable.

Generated columns and expression defaults are version/storage-engine-sensitive features. Keep their expressions deterministic where the use case requires repeatable behavior, review replication implications for nondeterministic defaults, and test DDL changes under the same server family used in production.

The next lesson focuses on identifier allocation and InnoDB locality: what AUTO_INCREMENT guarantees, what it does not guarantee, and how key shape affects every secondary index.

Authoritative references

• MySQL 8.4 Reference Manual — CREATE TABLE
• MySQL 8.4 Reference Manual — CHECK Constraints
• MySQL 8.4 Reference Manual — FOREIGN KEY Constraints
• MySQL 8.4 Reference Manual — Generated Columns
• MySQL 8.4 Reference Manual — Data Type Default Values
• MySQL 8.4 Reference Manual — TABLE_CONSTRAINTS