Lesson 12 intermediate

Constraints and Data Integrity

Let MySQL guard your data: NOT NULL and DEFAULT, primary keys, UNIQUE keys and how they treat NULL, CHECK constraints (enforced since 8.0.16), foreign keys with CASCADE, SET NULL and RESTRICT, the error codes each rule raises, disabling foreign key checks for bulk loads, the 8.4 rule against non-unique foreign key targets, and how to list and change constraints.

Applications have bugs, scripts get run twice and someone always edits production data by hand. Constraints are rules the database enforces on every write, whoever makes it. They are the cheapest bug prevention you will ever get. Orbit already uses several; this lesson explains them all and adds a few more.

NOT NULL and DEFAULT

NOT NULL says a column must always have a value; DEFAULT supplies one when an INSERT leaves it out:

INSERT INTO notes (user_id, title) VALUES (1, NULL);
ERROR 1048 (23000): Column 'title' cannot be null

Make columns NOT NULL unless “unknown” is a real, meaningful state. Nullable columns force every query to think about three-valued logic (lesson 7).

Defaults can be literals or, since MySQL 8.0.13, expressions in parentheses:

ALTER TABLE notes ALTER COLUMN body SET DEFAULT ('');
-- other examples: DEFAULT (UUID_TO_BIN(UUID(), 1)), DEFAULT (CURRENT_DATE + INTERVAL 30 DAY)

Primary keys

Every InnoDB table should have an explicit primary key: a column or set of columns that is unique and never NULL. InnoDB stores the table’s rows in primary-key order (the clustered index), so the primary key is also how every other index points to a row.

If you forget it, InnoDB silently adds a hidden 6-byte row id you cannot use in queries, and replication tools and online schema-change tools often refuse to work with the table. Some managed services require a primary key on every table; you can enforce that yourself with sql_require_primary_key = ON.

UNIQUE keys

A unique key rejects duplicate values:

INSERT INTO users (email, name) VALUES ('ana@orbit.dev', 'Another Ana');
ERROR 1062 (23000): Duplicate entry 'ana@orbit.dev' for key 'users.uq_users_email'

Two details matter:

  • Uniqueness follows the collation. With the default case-insensitive collation, ANA@orbit.dev counts as a duplicate of ana@orbit.dev, which is exactly what you want for emails.
  • A unique key allows many NULLs, because NULL is never equal to NULL. If “at most one row may have no value” is a rule, you need a different design.

A unique key can span several columns. Orbit’s note_tags primary key (note_id, tag_id) works that way: the same pair cannot appear twice, but each column alone can repeat.

CHECK constraints

A CHECK constraint rejects rows for which a condition is false. Titles must contain at least one non-space character, and emails must at least contain an @:

ALTER TABLE notes ADD CONSTRAINT chk_notes_title CHECK (CHAR_LENGTH(TRIM(title)) > 0);
ALTER TABLE users ADD CONSTRAINT chk_users_email CHECK (email LIKE '%_@_%');
INSERT INTO notes (user_id, title) VALUES (1, '   ');
ERROR 3819 (HY000): Check constraint 'chk_notes_title' is violated.

When you add a check, MySQL validates all existing rows and refuses the ALTER if any row breaks the rule, so clean the data first.

Note: MySQL parsed CHECK clauses for years but ignored them. Enforcement started in MySQL 8.0.16. A schema written for 5.7 may contain checks that its data has never respected; adding them back on 8.0.16+ will fail until you fix the rows.

Checks cannot use subqueries, non-deterministic functions such as NOW() or RAND(), stored functions or user variables, and cannot refer to other tables. They are for rules about a single row. A check that evaluates to NULL passes, just like in other databases, so combine it with NOT NULL when needed.

Foreign keys

A foreign key says that a value in the child table must exist in the parent table. Orbit’s notes.user_id references users.id:

INSERT INTO notes (user_id, title) VALUES (999, 'Orphan');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`orbit`.`notes`, CONSTRAINT `fk_notes_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE)

What happens when the parent row is deleted or its key changes is decided by the referential actions:

ActionWhen the parent is deleted / updated
RESTRICT / NO ACTION (default)Refuse while child rows exist (error 1451)
CASCADEDelete / update the child rows too
SET NULLSet the child column to NULL (it must be nullable)
SET DEFAULTParsed but rejected by InnoDB

Orbit cascades deletes from users to notes and from notes to note_tags. Try it with a throwaway user:

INSERT INTO users (email, name) VALUES ('temp@orbit.dev', 'Temp');
SET @temp = LAST_INSERT_ID();
INSERT INTO notes (user_id, title) VALUES (@temp, 'Will vanish');
DELETE FROM users WHERE id = @temp;
SELECT COUNT(*) FROM notes WHERE user_id = @temp;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Choose the action from what the relationship means. A note cannot live without its user, so CASCADE fits. An invoice must survive even if a customer account is closed, so RESTRICT is right there, and you would mark the customer inactive instead of deleting them.

Foreign key rules in MySQL:

  • Both columns must have the same type, size and signedness; text columns must also share a character set and collation.
  • The child column needs an index. MySQL creates one automatically if none exists, which is why Orbit names its own (idx_notes_user).
  • The referenced columns must be the parent’s primary key or a unique key. MySQL 8.4 enforces this by default (restrict_fk_on_non_standard_key = ON); older versions allowed references to any indexed column, which is nonstandard and now deprecated.
  • Cascading actions do not fire triggers on the child table (lesson 16).
  • Foreign keys work with InnoDB only.

Loading data with foreign keys

Restoring a dump table by table would fail if a child table were loaded before its parent. Dumps made by mysqldump disable checks for the session at the top of the file:

SET foreign_key_checks = 0;
-- load tables in any order
SET foreign_key_checks = 1;

Turning the checks back on does not re-validate rows loaded meanwhile. Only disable them for data you trust, such as your own backups, and only in your session, never globally.

Strict mode as a constraint

Lesson 5 showed that strict SQL mode turns silent truncation into errors. Think of it as a constraint on every column: values that do not fit the type are rejected instead of being changed. Keep STRICT_TRANS_TABLES in sql_mode.

Listing and changing constraints

Every constraint is described in information_schema:

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'orbit'
ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME;
+------------+-------------------+-----------------+
| TABLE_NAME | CONSTRAINT_NAME   | CONSTRAINT_TYPE |
+------------+-------------------+-----------------+
| note_tags  | fk_note_tags_note | FOREIGN KEY     |
| note_tags  | fk_note_tags_tag  | FOREIGN KEY     |
| note_tags  | PRIMARY           | PRIMARY KEY     |
| notes      | chk_notes_title   | CHECK           |
| notes      | fk_notes_user     | FOREIGN KEY     |
| notes      | PRIMARY           | PRIMARY KEY     |
| tags       | PRIMARY           | PRIMARY KEY     |
| tags       | uq_tags_name      | UNIQUE          |
| users      | chk_users_email   | CHECK           |
| users      | PRIMARY           | PRIMARY KEY     |
| users      | uq_users_email    | UNIQUE          |
+------------+-------------------+-----------------+
11 rows in set (0.00 sec)

Changing a constraint means dropping and re-adding it:

ALTER TABLE notes DROP FOREIGN KEY fk_notes_user;
ALTER TABLE notes ADD CONSTRAINT fk_notes_user
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE notes DROP CHECK chk_notes_title;       -- or DROP CONSTRAINT
ALTER TABLE users DROP INDEX uq_users_email;        -- unique keys are indexes
ALTER TABLE notes ALTER CHECK chk_notes_title NOT ENFORCED;

NOT ENFORCED keeps a check in the schema as documentation without applying it, useful during a migration. Naming every constraint, as Orbit does, makes these statements easy; unnamed ones get generated names like notes_ibfk_1 or notes_chk_1.

Quick reference

RuleSyntaxViolation
Requiredcol type NOT NULL1048
Default valueDEFAULT 0, DEFAULT (expr)—
IdentityPRIMARY KEY (id)1062
No duplicatesUNIQUE KEY name (col)1062
Row ruleCONSTRAINT name CHECK (expr)3819
ReferenceFOREIGN KEY (c) REFERENCES p (id) ON DELETE ...1452 child / 1451 parent
Bulk loadSET foreign_key_checks = 0; (session only)—

Exercise

A users table has a nullable UNIQUE column phone. Three users have no phone number. What happens?

  1. The second NULL fails with a duplicate-entry error
  2. MySQL stores the NULLs as empty strings
  3. The unique key is disabled for the column
  4. All three rows are accepted, because NULL is never equal to NULL
Show answer

Answer: D. Unique keys ignore NULLs for duplicate checking, so any number of rows may have NULL in a unique column.

Exercise

A table was created on MySQL 5.7 with CHECK (price > 0). After upgrading to MySQL 8.4, is the rule enforced?

  1. Yes, every CHECK clause is enforced from 8.0.16 on
  2. No, 5.7 discarded the clause, so the table has no check; add it again with ALTER TABLE
  3. Only for rows inserted after the upgrade
  4. Only if sql_mode includes STRICT_ALL_TABLES
Show answer

Answer: B. Before 8.0.16 MySQL parsed CHECK clauses and threw them away. An upgrade cannot enforce a rule that was never stored; ALTER TABLE … ADD CONSTRAINT adds it and validates the existing rows first.

Exercise

Which ON DELETE action fits an invoices.customer_id foreign key, where invoices must be kept for tax reasons?

  1. CASCADE
  2. SET NULL
  3. RESTRICT
  4. SET DEFAULT
Show answer

Answer: C. RESTRICT refuses to delete a customer that still has invoices. CASCADE would delete the invoices, and SET NULL would lose the link.

Key takeaways

  • Constraints are enforced on every write, whatever the source; use them generously.
  • Every InnoDB table needs an explicit primary key.
  • Unique keys follow the collation and allow multiple NULLs.
  • CHECK constraints are enforced from MySQL 8.0.16; foreign keys need matching types, an index and a unique parent key.
  • Disable foreign_key_checks only in your own session, only for trusted data.

FAQ

Should I enforce rules in the application or in the database?

Both. Validate in the application for friendly error messages, and enforce in the database so that scripts, admin tools and future services cannot bypass the rules.

Do foreign keys slow down writes?

A little: each insert into a child table looks up the parent row, which is a fast primary-key lookup. Large, sharded systems sometimes drop foreign keys for that reason, but for most applications the integrity guarantee is well worth it.

Why does adding a foreign key fail with error 1822 or 3780?

1822 means the parent column has no suitable unique index; 3780 means the column types do not match (often INT versus INT UNSIGNED, or different collations). SHOW ENGINE INNODB STATUS\G has a “LATEST FOREIGN KEY ERROR” section with details.

How do I find rows that break a rule I am about to add?

Query for them first: SELECT id FROM notes WHERE CHAR_LENGTH(TRIM(title)) = 0; for a check, or a LEFT JOIN ... WHERE parent.id IS NULL for a foreign key. Fix or remove those rows, then add the constraint.

↑ ↓ navigate Enter open Esc close