Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
Why DELETE and then INSERT? Why not UPDATE? Or MERGE ? –  ypercube Jun 26 '12 at 15:47
    
Postgres has deferred constraints but not deferred cascades and that is as per SQL Standard according to here –  Martin Smith Jun 26 '12 at 15:50
    
UPDATE or MERGE 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't MERGE 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

2 Answers 2

up vote 10 down vote accepted

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.

share|improve this answer
    
This sounds like a sensible idea. I'll do some investigating. As mentioned in my above comment though, performance might be an issue, but I guess I'll just have to test. –  growse Jun 27 '12 at 8:22
    
Merge wins. Fairly epic merge statement with 90 columns, but it works. –  growse Jun 27 '12 at 9:53
    
@growse: Good to hear! Per your other comment, how did it impact performance (just curious)? –  Jon Seigel Jun 27 '12 at 13:28
    
Well, I basically did the bulk insert into a temp table and then merged that with the original table. The bulk insert still takes around 6s, and the merge takes around 15-20s. The biggest issue is that I'm currently blanket updating on match, so I'm at least generating an update statement for each row in the table. This surely can be optimized, but I'd have to compare 90 columns for equality, and it didn't seem to be worth it. –  growse Jun 27 '12 at 13:45

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.