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.
| 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 |
|
|
|
|
|
|
code to disable all foreign keys in the db