How to disable all foreign keys in a database ?

Question

Post reply

How to disable all foreign keys in a database ?

SQL Guy 1

SSCoach

Points: 15979
More actions
September 2, 2010 at 10:30 am

#227333

I was given a task to insert/select tables from another database. All destination tables are empty now. Most of the tables have foreign keys. How can I disable all of them ? As well as how to re-enable again ?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Why disable the foreign keys? Just load the data into the tables in the correct order.

The following script will show you the correct order to load the data.

Find Table Reference Levels:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I agree with Michael.

Load the data in the correct order, or delete in the correct order.

Here is an alternative script to help with figuring out the order:

http://jasonbrimhall.info/2010/02/01/key-discovery-iii/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 3

Hmmm...

There are 38 tables, they are all from same schema. I created a cursor to script insert /select against source database. Putting them in correct order would be rather difficult. I will probably try to shuffle generated script batches in that order .

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

SQL Guy-482125 (9/2/2010)
Hmmm...
There are 38 tables, they are all from same schema. I created a cursor to script insert /select against source database. Putting them in correct order would be rather difficult. I will probably try to shuffle generated script batches in that order .

I understand where you are coming from. That is the reason people have come up with these kinds of scripts. I have a chain of 50+ tables related to some data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 5

I probably found even better solution. Sinse these tables initially clean in destination, I created 2 scripts in SSMS: tables and F.K., then I dropped all tables, ran script that creates anly tables, then I ran my script for insert/select generated from cursor against the source, and finally ran F.K. script.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Ok. What if you need to do this after it rolls to production?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95461 More actions · Answer 7

CirquedeSQLeil (9/2/2010)
Ok. What if you need to do this after it rolls to production?

Just drop the tables, add them, add the data, add the FKs. 😛 😉 :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

WayneS (9/2/2010)
CirquedeSQLeil (9/2/2010)
Ok. What if you need to do this after it rolls to production?
Just drop the tables, add them, add the data, add the FKs. 😛 😉 :w00t:

That reminds me...I have a report for work that needs me to do that very thing 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 9

That's a good question. Actually that exercise was like a "rehearsal" before deployment to production. There were too many errors, so we ran same things until install became clean.

Once installed on production, all the values will be entered through GUI, of course.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

SQL Guy-482125 (9/2/2010)
That's a good question. Actually that exercise was like a "rehearsal" before deployment to production. There were too many errors, so we ran same things until install became clean.
Once installed on production, all the values will be entered through GUI, of course.

K - that's good.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events