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

Brad Wery
382182126
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
29.5k291441646
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

Zote
1.7k364050
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×90

question asked: 27 Aug '10, 23:01

question was seen: 1,431 times

last updated: 17 May '12, 09:41