code to disable all foreign keys in the db

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 2008 Forums

Other SQL Server 2008 Topics

code to disable all foreign keys in the db

Author

Topic

rchiu5hk
Starting Member

7 Posts

Posted - 2008-12-29 : 05:43:34

What the code should be to disable all foreign keys constraints in the db1 and enable again after inserting all data in the db1?

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 05:57:23

make use of sys.foreign_keys catalog view for this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 06:03:10

something LIKE


--disabling fks
SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id)+
' NOCHECK CONSTRAINT '+  f.name 
FROM sys.foreign_keys AS f

--enabling fks
SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id)+
' CHECK CONSTRAINT '+  f.name 
FROM sys.foreign_keys AS f

copy this result, paste onto new window and execute

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 06:12:31

also


--disabling fks
SELECT 'ALTER TABLE ' + TABLE_NAME +
' NOCHECK CONSTRAINT '+  CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE='FOREIGN KEY'

--enabling fks
SELECT 'ALTER TABLE ' + TABLE_NAME +
' CHECK CONSTRAINT '+  CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE='FOREIGN KEY'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-29 : 11:26:01

It’s better to insert the data in the correct order without disabling the foreign keys. The script on the link below will find the correct order for the inserts.

Find Table Reference Levels
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

CODO ERGO SUM

rchiu5hk
Starting Member

7 Posts

Posted - 2008-12-29 : 22:59:38

Then How to disable all keys such as primary or foreign and all constraints ? As Always I delete or truncate or insert which make the process error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:43:38

quote:
Originally posted by rchiu5hk

Then How to disable all keys such as primary or foreign and all constraints ? As Always I delete or truncate or insert which make the process error.

extend the query above to include primary key and other constraints also

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources