MySQL constraints enforce rules for every writer, including scripts and future services. PRIMARY KEY identifies a row, UNIQUE prevents duplicate key values, NOT NULL requires a value, CHECK validates a condition in supported versions, and FOREIGN KEY protects referenced relationships under the chosen storage engine.
Applications can have bugs, imports can be messy, and multiple services may write to one database. Constraints keep essential rules close to the data.
Foreign keys prevent orphan records. Cascades define what happens to children when parents change, so the choice must match the business rule.
Adding a constraint to an existing table can fail if old rows violate the rule. Production migrations should audit, backfill, then enforce.
Choose constraint names that identify the table and rule so production errors are diagnosable. A UNIQUE constraint involving nullable columns follows MySQL null semantics and may allow multiple rows with NULL; use NOT NULL when absence is not valid.
Define foreign-key update and delete actions from domain meaning. CASCADE is useful for owned dependent rows but dangerous when relationships are not true ownership. Add constraints after cleaning existing data, and handle violations as expected conflicts rather than generic server failures.
| Constraint | Protects | Important Boundary |
|---|---|---|
| PRIMARY KEY | One stable row identity | Implies uniqueness and NOT NULL; one per table. |
| UNIQUE | Candidate-key uniqueness | Nullable columns can allow multiple NULL values. |
| NOT NULL | Required presence | Does not reject an empty string or zero. |
| CHECK | A row-level Boolean rule | A check that evaluates to UNKNOWN is not the same as FALSE. |
| FOREIGN KEY | Referenced-row existence | Actions must match ownership and both sides need compatible definitions. |
| DEFAULT | Value when a column is omitted | It is not validation and does not replace an explicit NULL. |
Before enforcing a new rule, query violations, decide whether to repair or quarantine them, and test the DDL against production-like volume. Large table changes can lock work or rebuild storage depending on version and operation, so inspect the execution algorithm and maintenance window.
Applications should map duplicate, null, check, and referential violations to stable domain responses. Do not parse localized error prose when a driver exposes an error code or SQLSTATE, and do not reveal internal table or constraint details to an untrusted client.
CREATE TABLE customers (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
customer_id BIGINT NOT NULL,
total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);The database rejects invalid date ranges regardless of which application performs the write.
ALTER TABLE subscriptions
ADD CONSTRAINT chk_subscription_dates
CHECK (ends_at IS NULL OR ends_at >= starts_at);
INSERT INTO subscriptions (starts_at, ends_at)
VALUES ('2026-07-14', '2026-07-01');The INSERT is rejected because ends_at is before starts_at.Yes. Constraints protect data from bugs, scripts, imports, and other applications.
Explore 500+ free tutorials across 20+ languages and frameworks.