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?
DELETE
and thenINSERT
? Why notUPDATE
? OrMERGE
? – ypercubeᵀᴹ Jun 26 '12 at 15:47UPDATE
orMERGE
would be valid approaches as well, I guess it'd just require more logic from the application doing the data ingest as to whether each row needs to be inserted, deleted or updated. Also, wouldn'tMERGE
be significantly slower than a bulk insert? I can currently insert 150,000 rows with 90 columns in about 4 seconds, so I don't know how long a merge operation would take. – growse Jun 27 '12 at 8:21