How Foreign-Key Delete Rules Affect API Test Isolation

Direct answer

The test-isolation problem

When an API test deletes a seeded configuration row while related attendance rows remain, the result depends on the foreign-key delete rule, not on the test name or fixture intent. The key question is whether the database is allowed to remove, preserve, or reject related child rows.

A seeded parent row and its related child rows must therefore be treated as one deletion decision. If the relationship is configured to prevent deletion while child rows exist, deleting the configuration row will fail until those references are gone.

CASCADE, RESTRICT, and NO ACTION compared

`CASCADE` deletes related child rows when the parent row is deleted. This rule permits parent cleanup to remove dependent data as part of the same operation.

`RESTRICT` aborts the delete when related child rows exist. For a test suite, that means a seeded configuration row cannot be deleted while its related rows remain.

`NO ACTION` is the default when no delete behavior is specified. It also prevents deletion when referenced rows remain, but it can be configured to defer the check until the end of a transaction.

Why deferred constraints fail at transaction end

A `NO ACTION INITIALLY DEFERRED` constraint does not necessarily raise its error at the moment the parent delete runs. It raises an error at the end of the transaction if referenced rows still exist.

That distinction matters during teardown. A test can appear to delete the seeded configuration successfully, then fail when the transaction completes because the related attendance rows were not removed.

A diagnostic checklist for seeded data and orphaned rows

For the team maintaining the API test suite, check these points:

• Identify which row is the parent and which rows reference it.
• Confirm the foreign-key delete behavior: `CASCADE`, `RESTRICT`, or `NO ACTION`.
• If the rule is `RESTRICT`, expect deletion to stop while child rows exist.
• If the rule is `NO ACTION`, check whether the constraint is deferred.
• If it is deferred, inspect the database state at transaction end, because that is when the remaining references cause the error.
• If the rule is `CASCADE`, account for the fact that deleting the parent also deletes related child rows.

The reliable fix is to align fixture teardown with the relationship’s defined delete behavior and verify the final transaction state.

What to do next

Review the cited sources and apply the relevant guidance described above.

Sources

• Cascade Deletes | Supabase Docs