MySQL / Reliable applications and analytical queries / Lesson 8

Constraints and purposeful indexes

Enforce valid relationships while selecting indexes from actual query shapes and write costs.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Explain CHECK, UNIQUE, NOT NULL, and foreign-key behavior.
Design composite indexes without promising result order or guaranteed use.

Database rules protect all writers

Use the shop fixture and inspect its constraints before changing anything. NOT NULL requires a value; CHECK validates an expression; UNIQUE prevents duplicate key values; a foreign key requires a matching referenced key for non-NULL references. These guarantees apply across applications and concurrent sessions. They complement friendly application validation, which can explain invalid input before a request reaches the database.

Understand NULL in constraints

A MySQL CHECK constraint accepts true or unknown and rejects false. CHECK (stock >= 0) alone therefore allows NULL; combining it with NOT NULL expresses the actual stock rule. A nullable UNIQUE key can contain multiple NULL entries, so it is not equivalent to a unique required identifier. InnoDB checks foreign keys immediately rather than offering deferred validation at transaction commit.

sql Inspect declared rules and available access paths

SHOW CREATE TABLE order_items;
SHOW INDEX FROM orders;

SHOW CREATE TABLE exposes the composite primary key, checks, and foreign keys; SHOW INDEX identifies indexes including those supporting relationships. Names and formatting may vary, so no fabricated complete server output is shown. Read the actual definition before proposing a redundant index or assuming a delete cascade exists.

Indexes exchange storage for access

InnoDB stores row data in the clustered primary-key structure. Secondary index records include the primary-key value, so a wide primary key increases secondary-index cost. Every insert, delete, and relevant update must maintain affected indexes. Indexing every column can therefore slow write-heavy workloads and consume buffer-pool space. Choose indexes for frequent, expensive query families rather than collecting them as general decorations.

Order columns from query requirements

A composite index on customer_id, placed_at, id can support equality on customer_id followed by a time range. Its leftmost prefixes are especially important; filtering only placed_at does not automatically get the same efficient lookup. Covering depends on all selected and filtered columns, including the primary key stored in secondary entries. The optimizer can still choose a scan when that is cheaper.

sql A query-shaped composite index

CREATE INDEX ix_orders_customer_time ON orders(customer_id,placed_at,id);
SELECT id,placed_at FROM orders
WHERE customer_id=1 AND placed_at>='2026-01-01'
ORDER BY placed_at,id;

Expected output

id | placed_at
101 | 2026-01-10 10:00:00
102 | 2026-01-11 11:00:00

This optional one-time lab DDL does not alter the fixture rows. The SELECT returns 101 then 102. The index is a candidate access path, not proof of selection. ORDER BY remains necessary even if the index happens to provide the requested order. Later lessons inspect EXPLAIN rather than inferring a plan from an index name.

Verify semantics before speed

Foreign-key column types must be compatible, including integer size and signedness; string relationships also need compatible character settings. Reference a primary or unique key rather than relying on older nonstandard InnoDB behavior. Before adding a constraint to existing data, identify violations and choose a repair policy. Adding an index or constraint is DDL, with operational locking and commit consequences separate from query correctness.

Common errors and fixes

NULL passes a supposedly required CHECK.

Why it happens: The expression evaluates to unknown, which CHECK accepts.
How to fix it: Add NOT NULL when absence is invalid and clean existing missing rows first.
Prevent it next time: Test true, false, and unknown cases for each domain rule.

ERROR 3780 (HY000) creating a foreign key.

Why it happens: Referencing and referenced integer columns use incompatible definitions, such as signed versus unsigned.
How to fix it: Align the definitions after auditing both value ranges.
Prevent it next time: Design key types once and reuse the exact definition.

ERROR 1061 (42000): Duplicate key name.

Why it happens: The optional CREATE INDEX script was repeated using the same index name.
How to fix it: Inspect SHOW INDEX and skip the already applied change.
Prevent it next time: Track schema changes as versioned migrations, separate from query rehearsals.

Check your understanding

1. Does an index on (customer_id, placed_at) guarantee WHERE placed_at >= ... is efficient?

Show worked solution

No. The query omits the leading customer component, so an ordinary leftmost-prefix lookup does not directly isolate the requested time range. Inspect the actual plan and workload before adding a time-leading index; any extra index adds maintenance cost.

2. Explain why stock needs both CHECK and NOT NULL.