Choose a foreign-key deletion policy that matches the record lifecycle
Deleting a customer can fail because invoices reference it, or can remove related rows if cascading deletion is configured. That difference is a data-retention decision, not just a migration syntax choice.
Protect the relationship deliberately
ALTER TABLE invoices
ADD CONSTRAINT invoices_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE RESTRICT;
This illustrative migration assumes compatible column definitions and existing valid relationships. Inspect existing orphan rows before applying a constraint. RESTRICT preserves the referenced customer while invoices depend on it.
Cascade is appropriate only for the intended ownership
A disposable parent-child record may legitimately cascade. Financial or audit history often needs a different lifecycle, such as deactivation or carefully designed archival. Do not add cascade simply to make an admin delete button stop returning an error.
Application validation can explain a blocked deletion nicely, while the database constraint protects the relationship under concurrency or writes outside the application. Keep both layers aligned.
Test deleting a parent with no children and one with children. Verify the expected child records remain or disappear according to the chosen policy. Include restore and archival behavior if the application uses soft deletion. A soft-delete timestamp does not invoke a physical foreign-key cascade, so the two mechanisms should not be described as interchangeable.