How to drop all FK contraints referencing a table and add them back?

Question

I use this command in SSMS 2016

EXEC sp_fkeys mytable1

and I have over 30 FK constraints referencing to a column in this table. How can I drop all of the constraints in one go, do some truncate work( need to copy a table from one server to another) and add them all back?

You can disable constraints but I don't think that lets you truncate. Delete is faster with them disabled. — paparazzo, Apr 17 '18 at 14:54
@paparazzo Delete is faster how? [Paul White](https://dba.stackexchange.com/a/30347/95107) has a great post on this, but maybe you are looking at this from an angle I'm not aware. — S3S, Apr 17 '18 at 14:55
@scsimon It is faster because it does not have to test for referential integrity. — paparazzo, Apr 17 '18 at 14:57

Racil Hilan · Answer 1 · 2018-04-17T15:40:57.043

1

Instead of dropping the constraints and adding them back, which is lots of unnecessary work, simply disable and enable them.

Put your code between these two lines:

ALTER TABLE mytable1 NOCHECK CONSTRAINT ALL

-- put your code here. Example: DELETE FROM mytable1

ALTER TABLE mytable1 CHECK CONSTRAINT ALL

Depending on your tables relationships, you may need to disable the constraints on other tables referencing your table. Example, if you need to disable the constraints on two tables:

ALTER TABLE mytable1 NOCHECK CONSTRAINT ALL
ALTER TABLE mytable2 NOCHECK CONSTRAINT ALL

-- put your code here. Example: DELETE FROM mytable1

ALTER TABLE mytable2 CHECK CONSTRAINT ALL
ALTER TABLE mytable1 CHECK CONSTRAINT ALL

If you want to disable and enable all the constraints in the database (not recommended unless you know what you're doing), you can use:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- put your code here. Example: DELETE FROM mytable1

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

edited Apr 17 '18 at 15:40

answered Apr 17 '18 at 15:02

Racil Hilan

24,690
13
50
55

The problem is I cant truncate using this. I keep getting the message "Cannot truncate table 'mytable1' because it is being referenced by a FOREIGN KEY constraint" – CodeMan03 Apr 17 '18 at 15:06
Yes, like paparazzo mentioned in the comments, use `DELETE` instead of `TRUNC`. – Racil Hilan Apr 17 '18 at 15:07
See my comment. I did not think this would let you truncate. – paparazzo Apr 17 '18 at 15:07
If I delete them how can I add them back? There are over 30 referenced tables for these constraints – CodeMan03 Apr 17 '18 at 15:09
@CodeMan03 he's saying DELETE the data instead of TRUNCATE TABLE. Not DELETE the constraints. However, I believe the problem with this answer is that it affects the constraints that are ON a table, not the contraints that REFERENCE the table. – Tab Alleman Apr 17 '18 at 15:12
1

Yea unfortunately I tried that too. I am getting The DELETE statement conflicted with the REFERENCE constraint 'FK_constraintName' It would be too tedious to go through each table to delete the FK constraint which is why I asked the original question – CodeMan03 Apr 17 '18 at 15:16
And that's why I told you to disable the constraints instead of deleting them. Yes, you will need to disable all the constraints on the other tables that reference your table. See my updated answer for an example. – Racil Hilan Apr 17 '18 at 15:29
@RacilHilan The problem is that your answer will disable the constraints on `mytable1`. It will not disable the FK constraints on all the other tables that reference `mytable1`. – Tab Alleman Apr 17 '18 at 15:34
@TabAlleman yes I know, but that should've been obvious to OP as he wanted to delete the constraints, so he must know what constraints to delete, which are exactly the same constraints to disable. Like in my update, you simply disable all constraints on the tables involved. – Racil Hilan Apr 17 '18 at 15:37
Ok, but that ignores the purpose of the question: "How can I drop all of the constraints in one go" – Tab Alleman Apr 17 '18 at 15:38
@TabAlleman what does "in one go" mean? Like in my second update, you can disable/enable all the constraints on the tables you want by simply having one line for each table. In my third update you can disable/enable all the constraints in the database in one single line. Does that count as "in one go"? :) – Racil Hilan Apr 17 '18 at 20:18
@RacilHilan I interpret "in one go" as meaning "in one query" as opposed to "in one query per table". If I had 30+ tables referencing my PK table, I'd be looking to avoid writing 30+ different queries too. – Tab Alleman Apr 18 '18 at 13:45
@TabAlleman Wow! 30+ tables referencing your PK table? That table must be the center of the world :). Look at the answer that the OP posted, does it look like done "in one query" to you? ;) There is just no way to do it in one query. Although the third solution in my answer looks like one query (i.e. one line), it is actually just handing the job to the stored procedure, so not really one query. – Racil Hilan Apr 18 '18 at 14:16

score 1 · Accepted Answer · edited Aug 14 '21 at 10:57

I found the answer I need to disable all foreign key constraints first then delete the data like paparazzo said then re-enable them.

Here are the scripts:

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;

Enable them back

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;

paparazzo · Answer 3 · 2018-04-17T15:29:15.150

0

See my first comment. Delete rather than truncate. Disable the constraints so the deletes are faster because they don't check for referential integrity. You need to disable on child and parent.

ALTER TABLE parent NOCHECK CONSTRAINT ALL;
ALTER TABLE child NOCHECK CONSTRAINT ALL;
delete parent where pk = 1
ALTER TABLE parent CHECK CONSTRAINT ALL
ALTER TABLE child CHECK CONSTRAINT ALL;

edited Apr 17 '18 at 15:29

answered Apr 17 '18 at 15:16

paparazzo

44,497
23
105
176

How to drop all FK contraints referencing a table and add them back?

3 Answers3