The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
382182026
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.3k287438645
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,425 times

last updated: 17 May '12, 09:41