CleanSql.com - T-SQL easy to readSafely dropping and recreating foreign key constraints per table-list
If you are trying to truncate some tables and are getting the error: Cannot truncate table 'YourTableName' because it is being referenced by a FOREIGN KEY constraint it is because both TRUNCATE TABLE and DROP TABLE commands are not allowed on tables being referenced by foreign key constraints. Other
- Tables referenced by an indexed view
- Published in transactional or merge replication
- Temporal tables
- Referenced by an EDGE constraint
- Enabled for Change Data Capture (something Microsoft forgot to mention as a restriction on their list)
Before truncating or dropping a table it is necessary to drop these FK constraints and - in case you want to preserve referential integrity - recreate them.
The scripts below allows you to safely drop and recreate all FK constraints for schemas/tables specified by the schema/table lists (parameters @ListOfSchemaNames and @ListOfTableNames) or - if you wish - per entire database by setting the parameter @DropAllFKPerDB = 1.
To read more about TRUNCATE TABLE command please refer to the Microsoft article or to an excellent explanation of how truncate works by Paul Randal.
The solution consists of 2 scripts which have to be run in the context of the same database but in separate sessions (why - explained in point 2.)
- Prepares and saves the drop/create constraint commands into a table [_ForeignKeyConstraintDefinitions], based on the delimited list of tables/schemas specified in parameters
@ListOfSchemaNamesand@ListOfTableNames(if you have same table names in more than one schema and you specify these schemas in the@ListOfSchemaNamesthe script will prepare the drop/recreate commands for all tables) - Prints out or executes the commands depending on the option chosen: you can choose either
@Dropor@Recreateand setting@Execute = 1will actually run the prepared commands and confirm each execution with- Executed Successfullyannotation. The 2nd part needs to be run in a separate session to avoid accidentally overwriting your [_ForeignKeyConstraintDefinitions] table with empty values after dropping all constraints.
If you want to ignore the @ListOfSchemaNames/@ListOfTableNames and generate drop/recreate commands for ALL FK constraints within the entire DB set the parameter: @DropAllFKPerDB = 1.
Here is the First Part::
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
USE [YourDbName]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO
/* ==================================================================================================================== */
/* Author: CleanSql.com © */
/*
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
*/
/* Create date: 2022-04-28 */
/* Description: Collects list of ForeignKeys referencing the tables specified in @ListOfSchemaNames/@ListOfTableNames */
/* ==================================================================================================================== */
/* Change History: */
/* -------------------------------------------------------------------------------------------------------------------- */
/* Date: User: Version: Change: */
/* -------------------------------------------------------------------------------------------------------------------- */
/* 2022-04-28 CleanSql.com 1.00 Created */
/* 2025-06-05 CleanSql.com 1.01 Improved option to search by source schema+table names, rather than target */
/* (i.e: to drop all FK defined on the tables you specify as input params) */
/* -------------------------------------------------------------------------------------------------------------------- */
/* ==================================================================================================================== */
DECLARE
@ListOfSchemaNames NVARCHAR(4000)
, @ListOfTableNames NVARCHAR(4000)
, @DropAllFKsPerDB BIT
, @delim CHAR(1)
, @ObjectID INT
, @ErrorMsg NVARCHAR(2047)
, @schema_id NVARCHAR(4000)
, @object_id NVARCHAR(4000)
, @start_search_sch INT
, @delim_pos_sch INT
, @schema_name SYSNAME
, @start_search_tbl INT
, @delim_pos_tbl INT
, @name_tbl SYSNAME
, @SqlEngineVersion INT
/* Set the list of Schemas and Tables you want to truncate; use a list of schemas and tables separated and terminated by the @delim charachter */
/* Below a sample list of tables and schemas from AdventureWorks2019 - fill in your values as you please */
SET @ListOfSchemaNames = 'Production;HumanResources;Person;'
SET @ListOfTableNames = 'Product;Employee;BusinessEntity;'
SET @delim = ';' /* character used to delimit and terminate the items in the lists above */
SET @DropAllFKsPerDB = 0 /* Set @DropAllFKsPerDB to = 1 ONLY if you want to ignore the @ListOfSchemaNames/@ListOfTableNames above
and generate drop/re-create commands for ALL FK constraints within the ENTIRE DB */
/* After the script executes CLOSE OR DISCONNECT THIS SESSION!!! - your commands are prepared in the table [_ForeignKeyConstraintDefinitions]
and you do not want to overwrite this table by re-running this script accidentally
Do not delete the [_ForeignKeyConstraintDefinitions] table untill you are 100% sure that you no longer need it */
DROP TABLE IF EXISTS [#SelectedObjectList];
CREATE TABLE [#SelectedObjectList]
(
[Id] INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
,[SchemaID] INT NOT NULL
,[SchemaName] SYSNAME NOT NULL
,[ObjectID] INT NOT NULL
,[TableName] SYSNAME NOT NULL
)
DROP TABLE IF EXISTS [dbo].[_ForeignKeyConstraintDefinitions]
CREATE TABLE [dbo].[_ForeignKeyConstraintDefinitions]
(
[LineId] INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
,[Foreign_Key_Id] INT UNIQUE NOT NULL
,[Drop_Constraint_Command] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,[Recreate_Constraint_Command] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
IF (@DropAllFKsPerDB <> 1) AND ((RIGHT(@ListOfSchemaNames, 1) <> @delim) OR (RIGHT(@ListOfTableNames, 1) <> @delim))
BEGIN
SET @ErrorMsg
= N'Strings: @ListOfSchemaNames and @ListOfTableNames have to end with the delimiter scpecified in @delim variable: [' + @delim + ']';
RAISERROR(@ErrorMsg, 16, 1);
RETURN;
END
/* ----------------------------------- Search through @ListOfSchemaNames: ----------------------------------- */
SET @start_search_sch = 0
SET @delim_pos_sch = 0
IF (@DropAllFKsPerDB <> 1)
BEGIN
WHILE CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + 1) > 0
BEGIN
SET @delim_pos_sch = CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + 1) - @start_search_sch
SET @schema_name = SUBSTRING(@ListOfSchemaNames, @start_search_sch, @delim_pos_sch)
SET @schema_id = NULL;
SELECT @schema_id = schema_id FROM sys.schemas WHERE name = @schema_name
IF (@schema_id IS NOT NULL)
/* ----------------------------------- Search through @ListOfTableNames: ----------------------------------- */
BEGIN
SET @start_search_tbl = 0
SET @delim_pos_tbl = 0
WHILE CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + 1) > 0
BEGIN
SET @delim_pos_tbl = CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + 1) - @start_search_tbl
SET @name_tbl = SUBSTRING(@ListOfTableNames, @start_search_tbl, @delim_pos_tbl)
SET @object_id = NULL;
SELECT @object_id = object_id FROM sys.objects WHERE schema_id = @schema_id AND name = @name_tbl
SET @ObjectID = NULL;
SET @ObjectID = OBJECT_ID('[' + @schema_name + '].[' + @name_tbl + ']');
IF (@object_id IS NOT NULL) AND (@ObjectID IS NOT NULL)
BEGIN
INSERT INTO [#SelectedObjectList] (
[SchemaID]
,[SchemaName]
,[ObjectID]
,[TableName]
)
VALUES (
@schema_id
,@schema_name
,@object_id
,@name_tbl
)
END
SET @start_search_tbl = CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + @delim_pos_tbl) + 1
END
END
/* ----------------------------------- End of Seaching through @ListOfTableNames -------------------------------- */
SET @start_search_sch = CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + @delim_pos_sch) + 1
END
END
ELSE
BEGIN
INSERT INTO [#SelectedObjectList] (
[SchemaID]
,[SchemaName]
,[ObjectID]
,[TableName]
)
SELECT SCHEMA_ID(TABLE_SCHEMA), TABLE_SCHEMA, OBJECT_ID(QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)), TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
END
/* ----------------------------------- End of Seaching through @ListOfSchemaNames -------------------------------- */
IF (@DropAllFKsPerDB <> 1) AND (SELECT COUNT(*) FROM [#SelectedObjectList]) < 1
BEGIN
BEGIN
SET @ErrorMsg
= N'Could not find any objects specified in the list of schemas: [' + @ListOfSchemaNames + N'] and tables: [' + @ListOfTableNames + N'] in the database: [' + DB_NAME(DB_ID()) + N'].';
RAISERROR(@ErrorMsg, 16, 1);
RETURN;
END
END
SELECT @SqlEngineVersion = CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1, 2) AS INT)
; WITH [cte] AS (
SELECT
fk.object_id AS [Foreign_Key_Id]
,fk.name AS [Foreign_Key_Name]
,[sch_src].[SchemaName] AS [Schema_Name_Src]
,(SELECT (OBJECT_NAME([fkc].parent_object_id))) AS [Table_Name_Src]
,[fkc].parent_column_id AS [Column_Id_Src]
,[col_src].name AS [Column_Name_Src]
,[sch_tgt].[SchemaName] AS [Schema_Name_Trgt]
,(SELECT (OBJECT_NAME([fkc].referenced_object_id))) AS [Table_Name_Trgt]
,[fkc].referenced_column_id AS [Column_Id_Trgt]
,[col_tgt].name AS [Column_Name_Trgt]
,[sch_tgt].[SchemaId] AS [Schema_Id_Trgt]
,[fk].delete_referential_action
,[fk].update_referential_action
,OBJECT_ID('[' + [sch_tgt].[SchemaName] + '].[' + OBJECT_NAME([fkc].referenced_object_id) + ']') AS [Object_Id_Trgt]
FROM sys.foreign_keys AS [fk]
CROSS APPLY (
SELECT
[fkc].parent_column_id,
[fkc].parent_object_id,
[fkc].referenced_object_id,
[fkc].referenced_column_id
FROM sys.foreign_key_columns AS [fkc]
WHERE 1 = 1
AND fk.parent_object_id = [fkc].parent_object_id
AND fk.referenced_object_id = [fkc].referenced_object_id
AND fk.object_id = [fkc].constraint_object_id
) AS [fkc]
CROSS APPLY (
SELECT [ss].name AS [SchemaName]
FROM sys.objects AS [so]
INNER JOIN sys.schemas AS [ss] ON [ss].schema_id = [so].schema_id
WHERE [so].object_id = [fkc].parent_object_id
) AS [sch_src]
CROSS APPLY (
SELECT [sc].name
FROM sys.columns AS [sc]
WHERE [sc].object_id = fk.[parent_object_id]
AND [sc].column_id = [fkc].[parent_column_id]
) AS [col_src]
CROSS APPLY (
SELECT [ss].schema_id AS [SchemaId]
,[ss].name AS [SchemaName]
FROM sys.objects AS [so]
INNER JOIN sys.schemas AS [ss] ON [ss].schema_id = [so].schema_id
WHERE [so].object_id = [fkc].referenced_object_id
) AS [sch_tgt]
CROSS APPLY (
SELECT [sc].name
FROM sys.columns AS [sc]
WHERE [sc].object_id = fk.[referenced_object_id]
AND [sc].column_id = [fkc].[referenced_column_id]
) AS [col_tgt]
INNER JOIN [#SelectedObjectList] AS [sol]
/* if you want to search by source schema+table names, rather than target (i.e: drop all FK defined on the tables you specify) comment out 2 lines below and uncomment the next one */
ON [sol].SchemaID = [sch_tgt].[SchemaId]
AND [sol].[ObjectID] = OBJECT_ID('[' + [sch_tgt].[SchemaName] + '].[' + OBJECT_NAME([fkc].referenced_object_id) + ']')
--ON [sol].[ObjectID] = OBJECT_ID('[' + [sch_src].[SchemaName] + '].[' + OBJECT_NAME([fkc].parent_object_id) + ']')
)
INSERT INTO [dbo].[_ForeignKeyConstraintDefinitions](
[Foreign_Key_Id]
,[Drop_Constraint_Command]
,[Recreate_Constraint_Command]
)
SELECT
[cte].[Foreign_Key_Id],
[Drop_Constraint_Command] =
'ALTER TABLE ' + QUOTENAME([cte].[Schema_Name_Src]) + '.' + QUOTENAME([cte].[Table_Name_Src])+' DROP CONSTRAINT ' + QUOTENAME([cte].[Foreign_Key_Name]) + ';',
[Recreate_Constraint_Command] =
CONCAT('ALTER TABLE ' + QUOTENAME([cte].[Schema_Name_Src]) + '.'+ QUOTENAME([cte].[Table_Name_Src])+' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME([cte].[Foreign_Key_Name]) + ' ',
CASE
WHEN @SqlEngineVersion < 14
/* For SQL Versions older than 14 (2017) use FOR XML PATH for all multi-column constraints: */
THEN 'FOREIGN KEY ('+ STUFF((SELECT ', ' + QUOTENAME([t].[Column_Name_Src])
FROM [cte] AS [t]
WHERE [t].Foreign_Key_Id = [cte].[Foreign_Key_Id]
ORDER BY [t].Column_Id_Trgt --This is identical to the ORDER BY in WITHIN GROUP clause in STRING_AGG
FOR XML PATH(''),TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2,'') + ' ) ' +
'REFERENCES ' + QUOTENAME([cte].[Schema_Name_Trgt])+'.'+ QUOTENAME([cte].[Table_Name_Trgt])+
' (' + STUFF((SELECT ', ' + QUOTENAME([t].[Column_Name_Trgt])
FROM [cte] AS [t]
WHERE [t].[Foreign_Key_Id] = [cte].[Foreign_Key_Id]
ORDER BY [t].[Column_Id_Trgt] --This is identical to the ORDER BY in WITHIN GROUP clause in STRING_AGG
FOR XML PATH(''),TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2,'') + ' )'
ELSE
/* For SQL Versions 2017+ use STRING_AGG for all multi-column constraints: */
'FOREIGN KEY ('+ STRING_AGG(QUOTENAME([cte].[Column_Name_Src]), ', ') WITHIN GROUP (ORDER BY [cte].[Column_Id_Trgt]) +') '+
'REFERENCES ' + QUOTENAME([cte].[Schema_Name_Trgt])+'.'+ QUOTENAME([cte].[Table_Name_Trgt])+' ('+ STRING_AGG(QUOTENAME([cte].[Column_Name_Trgt]), ', ') + ')'
END,
CASE
WHEN [cte].delete_referential_action = 1 THEN ' ON DELETE CASCADE '
WHEN [cte].delete_referential_action = 2 THEN ' ON DELETE SET NULL '
WHEN [cte].delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT '
ELSE ''
END,
CASE
WHEN [cte].update_referential_action = 1 THEN ' ON UPDATE CASCADE '
WHEN [cte].update_referential_action = 2 THEN ' ON UPDATE SET NULL '
WHEN [cte].update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT '
ELSE ''
END,
CHAR(13)+ 'ALTER TABLE ' + QUOTENAME([cte].[Schema_Name_Src])+'.'+ QUOTENAME([cte].[Table_Name_Src])+' CHECK CONSTRAINT '+ QUOTENAME([cte].[Foreign_Key_Name])+';')
FROM [cte]
GROUP BY
[cte].[Foreign_Key_Id]
,[cte].[Schema_Name_Src]
,[cte].[Table_Name_Src]
,[cte].[Foreign_Key_Name]
,[cte].[Schema_Name_Trgt]
,[cte].[Table_Name_Trgt]
,[cte].delete_referential_action
,[cte].update_referential_action
ORDER BY [cte].[Table_Name_Src]
IF (SELECT COUNT(*) FROM [dbo].[_ForeignKeyConstraintDefinitions]) < 1
BEGIN
BEGIN
SET @ErrorMsg
= N'Could not find any foreign keys referencing the tables specified in the list of schemas: [' + @ListOfSchemaNames + N'] and tables: [' + @ListOfTableNames + N'] in the database: [' + DB_NAME(DB_ID()) + N'].';
RAISERROR(@ErrorMsg, 16, 1);
RETURN;
END
END
SELECT
[Foreign_Key_Id]
,[Drop_Constraint_Command]
,[Recreate_Constraint_Command]
FROM [dbo].[_ForeignKeyConstraintDefinitions]
ORDER BY [Recreate_Constraint_Command], [Foreign_Key_Id]
The Second Part needs to be executed IN A SEPARATE SESSION from first script to avoid accidentally overwriting your [_ForeignKeyConstraintDefinitions] table with empty values after dropping all constraints:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
--USE [YourDbName]
--GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO
/* ==================================================================================================================== */
/* Author: CleanSql.com © */
/* Create date: 2022-04-28 */
/* Description: Drops ForeignKeys collcted in [dbo].[_ForeignKeyConstraintDefinitions] */
/* ==================================================================================================================== */
/* Change History: */
/* -------------------------------------------------------------------------------------------------------------------- */
/* Date: User: Version: Change: */
/* -------------------------------------------------------------------------------------------------------------------- */
/* 2022-04-28 CleanSql.com 1.0 Created */
/* -------------------------------------------------------------------------------------------------------------------- */
/* ==================================================================================================================== */
/* ==================================================================================================================== */
/* !!! ATTENTION - from this line to the end: run this code-section IN A SEPARATE SESSION !!! from first script
to avoid accidentally overwriting your [_ForeignKeyConstraintDefinitions] table with empty values
after dropping all constraints */
DECLARE
@Foreign_Key_Id INT
,@Drop_Constraint_Command NVARCHAR(MAX)
,@Recreate_Constraint_Command NVARCHAR(MAX)
,@Command NVARCHAR(MAX)
,@Drop BIT
,@Recreate BIT
,@Execute BIT
,@RowCount INT
,@LineId INT = 1
SET @Drop = 1
SET @Recreate = 0
/* CAUTION!!!! SETTING @Execute = 1 WILL EXECUTE ALL @Drop OR @Recreate COMMANDS: */
SET @Execute = 0 /* 0 = Print out the @Command only */
SET XACT_ABORT ON
IF (@Execute = 0)
BEGIN
PRINT('-----------------------------------------------------------------------------------------');
PRINT('Below is the PRINTOUT ONLY of the commands to be executed once the @Execute is set to = 1');
PRINT('-----------------------------------------------------------------------------------------');
END
SELECT @RowCount = COUNT(LineId) FROM [dbo].[_ForeignKeyConstraintDefinitions]
WHILE @LineId <= @RowCount
BEGIN
SELECT
@Foreign_Key_Id = [Foreign_Key_Id]
,@Drop_Constraint_Command = [Drop_Constraint_Command]
,@Recreate_Constraint_Command = [Recreate_Constraint_Command]
FROM [dbo].[_ForeignKeyConstraintDefinitions]
WHERE LineId = @LineId
IF (@Drop = 1 AND @Recreate = 0)
BEGIN
SET @Command = @Drop_Constraint_Command
END
ELSE IF (@Drop = 0 AND @Recreate = 1)
BEGIN
SET @Command = @Recreate_Constraint_Command
END
ELSE
BEGIN
RAISERROR('Set one (and only one) of the parameters: @Drop or @Recreate = 1 so that either one of the actions is selected', 16, 1);
RETURN;
END
IF (@Execute = 0)
BEGIN
PRINT(@Command)
END
IF (@Execute = 1)
BEGIN
EXECUTE(@Command);
PRINT(CONCAT(@Command, ' - Executed Successfully'));
END
SET @LineId = @LineId + 1;
END
/* ======================================================================================================================= */
/* If you are absolutely sure that you no longer need the [dbo].[_ForeignKeyConstraintDefinitions] table then drop it:
DROP TABLE [dbo].[_ForeignKeyConstraintDefinitions]
*/
If you successfully drop the constraints then you’ll be able to run truncate command however, be careful: truncating the target table means that the source table now contains data in the column that is referencing your (already truncated) target, which opens up a
For example: if your BusinessEntity table (target) is being referenced by a FK in BusinessEntityAddress (source) like this:
then sure, you could drop that FK reference in order to truncate the target (BusinessEntity), but once BusinessEntity is empty and BusinessEntityAddress contains entries (in BusinessEntityID column) pointing to it, you won’t be able to recreate that FK unless you use WITH NOCHECK option. The option tells SQL Server to trust the existing data as valid, without performing any checks. SQL Server marks the constraint as trusted and enforceable for future inserts or updates, however, it does not retroactively validate the existing data in BusinessEntityAddress against (now empty) BusinessEntity. Any new data modifications of BusinessEntityAddress will be subject to the recreated constraint-check but the existing data in that table, which may (as is the case here) violate the constraint, is essentially “grandfathered in”. What is even worse, after recreating the FK using WITH NOCHECK option you could accidentally (and with no errors) re-populate BusinessEntity using records, that (for example):
- do have matching
BusinessEntityID’s, but come from a different source-table/database, therefore contain totally different data in all other columns. - get imported from a backup CSV flat file, which somebody conveniently re-sorted for you with the key column
BusinessEntityIDbeeing auto-generated as an IDENTITY column, rather than getting imported as in the original - this could completely conceal the issue for days, until you find out that all invoices got mailed to wrong addresses.
There is another drawback of using WITH NOCHECK option when recreating your Foreign Keys: if the target table gets included in a schema-bound, view with an index on top of it, inserting a new record into BusinessEntity when all the foreign keys are trusted looks like a straightforward clustered index insert:
However, the same insert when all the foreign keys are “untrusted” requires SQL to check each linked table first, to make sure any existing records in those linked tables are included (btw. this execution plan will persist even after you use Jason Strate’s script to upgrade all untrusted FK to trusted and recreate your schema-bound view/index):
To avoid such scenarios it is better to:
- save the data of the target into a temp table making sure the FK column is included
- drop all FKs referencing your table
- truncate the table
- re-import the contents of the temp table back into the original table
- recreate the FK using
WITH CHECKoption
If you are absolutely sure about the safety of your data and in addition to Foreign Keys there are other restrictions preventing your truncate the stored procedure sp_ForceTruncate will truncate your table(s) based on either:
- a delimiter-separated list of schemas/tables you specify or
- a sledge-hammer option: truncate ALL your tables within the entire db by setting the parameter
@TruncateAllTablesPerDB = 1(use at your own risk)