How can foreign key constraints be temporarily disabled using T-SQL?

sql-server tsql foreign-keys constraints
Ray picture Ray · Oct 1, 2008 · Viewed 631.4k times · Source

Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?

Answer

kristof picture kristof · Oct 2, 2008

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Related questions

Two foreign keys reference one table - ON UPDATE SET NULL doesn't work

I've got two foreign keys in a table. Let's assume that table is called News and has foreign keys updatedById and createdById, both of which point to userId in table Users. Now I want to set to NULL foreign keys …

Read More
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)? I know that I can either Use a DELETE without a …

Read More
How can I INSERT data into two tables simultaneously in SQL Server?

Let's say my table structure looks something like this: CREATE TABLE [dbo].[table1] ( [id] [int] IDENTITY(1,1) NOT NULL, [data] [varchar](255) NOT NULL, CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC) ) CREATE TABLE [dbo].[table2] ( [id] [int] IDENTITY(1,1) NOT NULL, [table1_…

Read More