I'm creating a data transfer utility. One thing it has to do is disable foreign keys while deleting and inserting data. Is there a way to disable foreign keys on referencing tables so the constraint isn't evaluated? I'm hoping there is a way to switch it off as oppose to dropping and creating. Thanks, Scenario: Address AddressID Customer AddressID - Foreign key to Address Which do I do first? If I delete from Customer then insert I may be inserting records with an AddressID that doesn't exist in Address (foreign key constraint). If I delete from Address (automatic foreign key constraint). I can't delete everything table then start inserting. It has to be a Delete\Insert pair. asked 27 Aug '10, 23:01 Brad Wery |
You might be able to use the option wait_for_commit to prevent the evaluation of any foreign key relationship until the next commit. This should suffice if your deletes/inserts can be done within a single transaction. answered 28 Aug '10, 11:20 Volker Barth The tables are processed individually (one does not know of the other). This won't work. Thanks though. |
Some time ago I need something like this. I solved this problem calculating and generating an import order. answered 27 Aug '10, 23:44 Zote To refresh the table I may do a delete and then an insert. The only way to do this would be to disable the foreign keys. I'll update the question with a scenario. |
Have you found a solution to this one?
This question is two years old. There is no guarantee Brad will see your comment.
You should ask a new question, and explain why WAIT_FOR_COMMIT won't work for you... Brad wasn't clear in his response.