I had a question about what the expected and actual behaviour for the following scenario is.
The scenario is one where a database table (
[table1]) is cleared and reloaded every day. The table has an
id column which is reference by several other tables using foreign keys. If I set the foreign key
ON DELETE action to
CASCADE, obviously this will delete rows in the other tables if I issue a single
DELETE FROM [table1] command on this table. However, what would happen if I were to delete all the rows, and then re-insert the same rows with the same IDs under the same transaction? Will this trigger the cascade mid-transaction, or will the foreign-key reconciliation happen once I've called commit?
Obviously, I'm thinking SQL Server here, but I'm wondering if this behaviour is consistent across other DBs as well.
In the event that the cascade is triggered even in the middle of the transaction, what would be the best way to go about managing foreign keys and relationships with a table which is completely cleared and re-loaded every day?