Foreign Key
A foreign key is a database rule. It makes one column point to a valid key in the same or another table. `null` follows the rule chosen for that column.
Another example
Every `invoice_line.invoice_id` must point to an existing invoice. Deleting a draft also deletes its lines, but a posted invoice cannot be deleted and needs a reversal process. Bulk imports hold bad rows aside instead of turning off the foreign key forever.
Main parts
- 01Referencing columns
- 02Referenced candidate key
- 03Update/delete action
Use it when
Use a foreign key when a database relationship must never point to a missing parent. Choose its update and delete actions with care.
Do not use it when
Under concurrency, do not rely only on app checks. Before cascade deletion crosses a boundary, review data retention and audit duties.
Name used in code
FOREIGN KEY (…) REFERENCES … ON UPDATE/DELETE … Before you ship
Check contracts and invariants, authorization, and input limits. Test retries, one-time effects, timeouts, and partial failures. Verify data integrity, compatibility, monitoring, recovery, and rollback.
Outcome: Design or review Foreign Key for this system while keeping data correct, secure, and easy to operate. User context: Every `invoice_line.invoice_id` must point to an existing invoice. Deleting a draft also deletes its lines, but a posted invoice cannot be deleted and needs a reversal process. Bulk imports hold bad rows aside instead of turning off the foreign key forever. Technical concept or control: Foreign Key. Why it fits: Use a foreign key when a database relationship must never point to a missing parent. Choose its update and delete actions with care. Do not use it when: Under concurrency, do not rely only on app checks. Before cascade deletion crosses a boundary, review data retention and audit duties. Engineering requirements: Define inputs, outputs, contracts, and rules that must always hold. Set authorization and input checks. Define failures, timeouts, retries, one-time effects, data moves, and compatibility limits. Operations: Add useful signals and logs that hide sensitive data. Set capacity limits and write an operator guide. Test recovery and rollback. Acceptance criteria: Contract and failure-path tests pass. Data and authorization rules still hold during retries and partial failures. Check performance limits, monitoring, recovery, and rollback. Evidence and limits (evidence boundary): It is official for that source. Other platforms or teams may use the term in another way. Unknowns to confirm: Where the code runs, data stores, traffic shape, consistency needs, and the security boundary. Also confirm the compatibility period, service goals, and rollback limits.
Check this request
Review the current use of Foreign Key. Definition: A foreign key is a database rule. It makes one column point to a valid key in the same or another table. `null` follows the rule chosen for that column. Release checks: Check contracts and invariants, authorization, and input limits. Test retries, one-time effects, timeouts, and partial failures. Verify data integrity, compatibility, monitoring, recovery, and rollback. Before changing code, report the evidence you found, gaps, severity, and the smallest safe fix.