Turning off *All* Foreign Key Contraints

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Turning off *All* Foreign Key Contraints

Author

Topic

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-02-08 : 11:43:21

I have a script that creates the following for all tables in the database:
ALTER TABLE [dbo].[EventCategoryEvent] NOCHECK CONSTRAINT ALL

Will this not turn off foreign key constraints? I am doing a major match & merge of data and need to turn off all constraints while its running.

If that doesn't turn off FK's how would I go about doing that batch style?

error: Cannot truncate table 'Organization' because it is being referenced by a FOREIGN KEY constraint.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 12:18:52

Script all the constraints to be dropped, drop them all at the beginning of the script and recreate them at the end..

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-02-08 : 12:30:53

Thats exactly what I have done. I get the error I posted when I try and truncate the table. I use a script that generates a NOCHECK CONSTRAINT ALL statement for every table in the Database. I still get the error.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 12:35:42

You have to supply the constraint name.

The examples below are from SQL Server Books Online:


-- Disable a constraint
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap


-- Enable a constraint
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap

CODO ERGO SUM

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-02-08 : 12:38:56

Should ALTER TABLE BLAH NOCHECK CONSTRAINT ALL turn off all contraints for table blah?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 12:39:31

Will a NOCHECK even work though? I thought you had to actually drop the constraint in order to run TRUNCATE.

Tara Kizer
aka tduggan

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 12:39:49

Sorry, I missed the key word in my reply, there should have been seperately after dropped..

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-02-08 : 12:54:12

I suppose I could try delete from TABLE. Would that work? Essentially I am merging a bunch of PK's then removing duplicates then truncating and filling the original table with the new data.

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 04:20:55

"I suppose I could try delete from TABLE"

Should work. You need to delete all child tables first, then parent tables. If in doubt loop round until all tables have 0 rows (and "catch" any errors from "Referential integrity errors" when your code tries to delete a parent record that still has children!)

Uses way more log space than TRUNCATE TABLE ...

Other alternative, possibly, is to BCP the data out [preferably in "Native" format, which will retain all datatype info], DROP the tables, reCREATE the tables appropriately [without FKs], re-import the data with BCP then create the FKs

(Make sure the data is exported in the new PK order [actually the order of the Clustered Index], and use the BCP hint on Import to alert SQL that the import file is pre-sorted)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources