Turn Off Constraints Temporarily (Ms Sql)

Turn off constraints temporarily (MS SQL)

You can disable FK and CHECK constraints only in SQL 2005+. See ALTER TABLE

ALTER TABLE foo NOCHECK CONSTRAINT ALL

or

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.

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

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.

Temporarily disable all foreign key constraints

To disable foreign key constraints:

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + N' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sys.sp_executesql @sql;

To re-enable:

DECLARE @sql nvarchar(max) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + N' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sys.sp_executesql @sql;

However, you will not be able to truncate the tables, you will have to delete from them in the right order. If you need to truncate them, you need to drop the constraints entirely, and re-create them. This is simple to do if your foreign key constraints are all simple, single-column constraints, but definitely more complex if there are multiple columns involved.

Here is something you can try. In order to make this a part of your SSIS package you'll need a place to store the FK definitions while the SSIS package runs (you won't be able to do this all in one script). So in some utility database, create a table:

CREATE TABLE dbo.PostCommand(cmd nvarchar(max));

Then in your database, you can have a stored procedure that does this:

DELETE other_database.dbo.PostCommand;

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'ALTER TABLE ' 
   + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
   + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
   + ' ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY (' 
   + STUFF((SELECT ',' + c.name
    FROM sys.columns AS c 
        INNER JOIN sys.foreign_key_columns AS fkc 
        ON fkc.parent_column_id = c.column_id
        AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', 'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + 
QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id))
+ '(' + 
STUFF((SELECT ',' + c.name
    FROM sys.columns AS c 
        INNER JOIN sys.foreign_key_columns AS fkc 
        ON fkc.referenced_column_id = c.column_id
        AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'') + ');
' FROM sys.foreign_keys AS fk
WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;

INSERT other_database.dbo.PostCommand(cmd) SELECT @sql;

IF @@ROWCOUNT = 1
BEGIN
  SET @sql = N'';

  SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
    + ' DROP CONSTRAINT ' + fk.name + ';
  ' FROM sys.foreign_keys AS fk;

  EXEC sys.sp_executesql @sql;
END

Now when your SSIS package is finished, it should call a different stored procedure, which does:

DECLARE @sql nvarchar(max);

SELECT @sql = cmd FROM other_database.dbo.PostCommand;

EXEC sys.sp_executesql @sql;

If you're doing all of this just for the sake of being able to truncate instead of delete, I suggest just taking the hit and running a delete. Maybe use bulk-logged recovery model to minimize the impact of the log. In general I don't see how this solution will be all that much faster than just using a delete in the right order.

In 2014 I published a more elaborate post about this here:

• Drop and Re-Create All Foreign Key Constraints in SQL Server

Disable constraints temporary in SQL server

-- disable the check_sale constraint in the employee table
ALTER TABLE employee NOCHECK CONSTRAINT check_sale

-- enable the check_sale constraint in the employee table
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT check_sale

if your script span multiple tables you can retrieve all the constraints and disable all of them.

USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

http://www.mssqlcity.com/Articles/General/using_constraints.htm

How can I temporarily disable a foreign key constraint in MySQL?

Try DISABLE KEYS or

SET FOREIGN_KEY_CHECKS=0;

Make sure to

SET FOREIGN_KEY_CHECKS=1;

after.

How can I temporarily disable all constraints in a Table in Firebird 2.1?

AFAICT foreign keys can't be disabled in Firebird up to 2.1.

You'd have to drop and recreate them.

There's a similar thread on Devshed.

---

Related Topics