Will a delete then insert within a transaction cascade?

Question

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?

Answer 1

I'm only familiar with SQL Server:

Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.

If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use UPDATE or, preferably MERGE) instead of blowing everything away and starting from scratch.

I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.

Answer 2

No, it will not. When in doubt, test it out:

create table Parent
(
    ParentId int identity(1, 1) not null primary key clustered,
    Data nvarchar(30) not null default 'some data here...',
    Num int not null
)
go

create table Child
(
    ChildId int identity(1, 1) not null,
    ParentId int not null foreign key references Parent(ParentId) on delete cascade
)
go

insert into Parent(Num)
values(15), (17), (23)
go

select *
from Parent

insert into Child(ParentId)
values(2), (3)
go

select *
from Child

begin tran
    delete from Parent
    where ParentId = 3

    set identity_insert Parent on
    insert into Parent(ParentId, Num)
    values(3, 64)
    set identity_insert Parent off
commit tran

(You should probably step through that code instead of just a blanket execution). Then by doing a simple

select *
from Child

You will see that the row containing ParentId with a value of 3 is no longer in the table. So the answer is, no. It won't be available still.

Now, if you were to rollback the transaction that did the delete with an on delete cascade, then the record would still be in the child table. By viewing sys.dm_tran_locks DMV, there will be an exclusive lock on the table containing the foreign key constraint.

As for other RDBMSs, I can't say for sure. Only SQL Server.