Asked  7 Months ago    Answers:  5   Viewed   52 times

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

 Answers

99

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.

Tuesday, June 1, 2021
 
waylaidwanderer
answered 7 Months ago
33

You would need to manage the referential constraint across databases using a Trigger.


Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.

Example:

Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END

Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.

Sunday, June 6, 2021
 
Puneet
answered 6 Months ago
86

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 + ' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC 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 + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC 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('.', 'nvarchar(max)'), 1, 1, '')
+ ') 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('.', 'nvarchar(max)'), 1, 1, '') + ');
' 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 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 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
Sunday, June 27, 2021
 
davidb
answered 6 Months ago
13

That isn't quite an SQL file, that contains a bunch of MySQL-specific stuff some of which SQLite will accept and some it won't. We'll start at the top.

You don't need create database or use with SQLite. If you want to create a database just name it when you run sqlite3 from the command line:

$ sqlite3 db_name.sqlt < your_sql.sql

If db_name.sqlt exists then it will be used, if it doesn't exist then it will be created. So create database and use are implied by how you run sqlite3. You might need to use a different extension depending on what Python wants to see.

The backticks for quoting are a MySQLism, double quotes are the standard quoting mechanism for identifiers. Lucky for you, SQLite will accept them so you can leave them alone.

SQLite won't know what int(10) unsigned means, you'll have to remove the unsigned before SQLite will accept it. SQLite also won't know what ENGINE=InnoDB DEFAULT CHARSET=utf8 means so you'll have to remove that as well.

You'll probably run into other things that MySQL is happy with but SQLite is not. You'll have to try it and fix it and try it and fix it until it works. Or try to find a tool that can translate between databases for you, I always do these sorts of things by hand or using one-off scripts so I don't know of any tools that can help you.

Monday, August 9, 2021
 
rvictordelta
answered 4 Months ago
52

No. SQL Server does not and never has automatically created indices on foreign key columns. It's a wide spread urban myth - but it's nothing more than that - a myth.

But it's an accepted best practice to do so - so that's one of the most basic recommendations for index tuning - put indices on your foreign key columns.

See Kimberly Tripp's excellent When did SQL Server stop putting indexes on foreign key columns? blog post for more background info.

Tuesday, November 30, 2021
 
Raven
answered 4 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share