0

I've got this script

TRUNCATE TABLE [dbo].[TableOne]
TRUNCATE TABLE [dbo].[TableTwo]

SET IDENTITY_INSERT [dbo].[TableOne] ON
...Insert stuff....
SET IDENTITY_INSERT [dbo].[TableOne] OFF

SET IDENTITY_INSERT [dbo].[TableTwo] ON
...Insert other stuff....
SET IDENTITY_INSERT [dbo].[TableTwo] OFF

But I can't truncate TableTwo because of a foreign key constraint. But that's okay, because I am truncating both the relevant tables and inserting fresh data.

So is it possible to just temporarily disable the foreign key or something along those lines, and then reactivate it at the end of the script?

disable FK

[the script]

enable FK
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Petter Petter
  • 93
  • 1
  • 8
  • If the linked field can accept null values (or you can temporarily set it so) you can temporarily set the On Delete property to Set to Null. – SunKnight0 Jun 08 '16 at 19:08

1 Answers1

0

Yes it is possible. You should to do the following preferably in a transaction:

Drop the constraints

Truncate the table

Recreate the constraints.

To disable:

https://msdn.microsoft.com/en-us/library/ms175041.aspx

To enable:

http://www.techonthenet.com/sql_server/foreign_keys/enable.php

There are many other examples for specific syntax.

William Xifaras
  • 5,212
  • 2
  • 19
  • 21