The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.



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%20Wery's gravatar image

Brad Wery
accept rate: 0%

edited 28 Aug '10, 00:58

Have you found a solution to this one?

(17 May '12, 07:36) henginy
Replies hidden

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.

(17 May '12, 09:41) Breck Carter

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.

permanent link

answered 28 Aug '10, 11:20

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

The tables are processed individually (one does not know of the other). This won't work. Thanks though.

(31 Aug '10, 01:07) Brad Wery

Some time ago I need something like this. I solved this problem calculating and generating an import order.

permanent link

answered 27 Aug '10, 23:44

Zote's gravatar image

accept rate: 43%

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.

(28 Aug '10, 00:54) Brad Wery
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 27 Aug '10, 23:01

question was seen: 1,502 times

last updated: 17 May '12, 09:41