Overview
Foreign keys let the database enforce relationships that application code might otherwise forget. They are valuable for preventing orphan records, but they also introduce operational behavior that should be explicit in the schema and application design.
Start with the workload and the invariant
Before adding a constraint, inspect existing data for orphans and duplicates. Ensure parent and child columns have compatible types, signedness, character set, and collation where relevant. In MySQL, referenced and referencing tables must use a supporting engine, and referenced keys must meet the server's index requirements.
Practical example
SELECT child.parent_id
FROM child
LEFT JOIN parent ON parent.id = child.parent_id
WHERE child.parent_id IS NOT NULL
AND parent.id IS NULL;
Choose ON DELETE behavior from the business rule. RESTRICT or NO ACTION prevents deleting a parent while dependent rows exist. CASCADE can be appropriate for dependent data that has no independent meaning, such as line items belonging to a draft. SET NULL requires a nullable child column and a clear meaning for an unassigned relationship.
Trade-offs and failure modes
Do not use cascading deletion as a substitute for an explicit retention policy. A single parent deletion can remove a large number of rows and create a long-running transaction. For high-volume relationships, consider a controlled archival or deletion workflow with observable batches.
Verification and rollout
Add constraints through a staged migration: clean invalid data, add supporting indexes, apply the constraint, and verify it. Test application deletion and update flows, including error handling when a constraint rejects an operation. Ensure the user receives a meaningful response rather than a generic server error.
Engineering checklist
Foreign keys complement, rather than replace, authorization checks. A valid relationship does not prove that the current user is allowed to access the parent record. Keep tenant and ownership checks in the application query and enforce uniqueness where the data model requires it.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.