NOCHECK 不会禁用外键引用 [英] NOCHECK does not disable foreign key references
本文介绍了NOCHECK 不会禁用外键引用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
表创建脚本:
CREATE TABLE [dbo].[details](
[id] [int] NULL,
[details] [varchar](max) NULL
)
CREATE TABLE [dbo].[name](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[name] [varchar](max) NULL,
CONSTRAINT [PK__name__3213E83F0D384EE4] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[details] WITH NOCHECK ADD CONSTRAINT [id] FOREIGN KEY([id])
REFERENCES [dbo].[name] ([id])
GO
ALTER TABLE [dbo].[details] NOCHECK CONSTRAINT [id]
GO
表填充脚本
insert into name(name) values (newid())
go 100
insert into details (id,details) values(RAND()*10,newid())
Go 1000
表格截断脚本
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_msforeachtable "truncate table ?"
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
也试过了
alter table name nocheck constraint all
truncate table name
这两种情况都不允许我截断表，因为存在外键引用
Both the cases don't allow me to truncate the table since a foreign key reference is there
错误信息:
无法截断表 'dbo.name'，因为它正被一个引用外键约束.
Cannot truncate table 'dbo.name' because it is being referenced by a FOREIGN KEY constraint.
有没有办法禁用FK约束，删除数据库中的所有数据并重新初始化身份，然后重新启用FK约束?
Is there a way to disable FK constraints, delete all data in the database and reinitialize identities, then reenable FK constraints?
推荐答案
我使用了以下两个脚本来禁用外键.我还没有创建它们，我是在互联网上找到它们的，但它们在过去几年中对我来说效果很好.不幸的是，我再也找不到它们的来源了.
I used the following two scripts for disabling foreign keys. I have not created them, I found them on interwebs, but they worked perfectly for me for the last few years. Unfortunately I can't find the source of them any longer.
references_sp.sql 是需要在其他脚本运行之前创建的存储过程.
references_sp.sql is a stored procedure that need to be created before the other script runs.
references_run.sql 是一个脚本，用于生成禁用/启用外键的脚本.
references_run.sql is a script that generates a script for disabling/enabling foreign keys.
请注意，运行此处提供的脚本是安全的 - 它不会修改数据库(除了创建然后删除临时表)，它只会生成另一个脚本，该脚本删除并重新创建外键.
Note that it's safe to run the script provided here - it does not modify the database (apart from creating and then deleting temporary tables) it just generates another script that drops and re-create the foreign keys.
现在是脚本.
references_sp.sql:
references_sp.sql:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*********************************************************************************************
Copyright SQLServerNation.com
* Author: Tim Chapman
***********************************************************************************************/
CREATE PROCEDURE [dbo].[sp_ShowForeignKeyObjects]
AS
SELECT
OBJECT_NAME(constid) AS ConstraintName,
OBJECT_SCHEMA_NAME(fkeyid) + '.' + OBJECT_NAME(fkeyid) + '.' + COL_NAME(fkeyid, fkey) AS ForeignKeyObject,
OBJECT_SCHEMA_NAME(rkeyid) + '.' + OBJECT_NAME(rkeyid) + '.' + COL_NAME(rkeyid, rkey) AS ReferenceKeyObject,
COL_NAME(fkeyid, fkey) AS ForeignKeyColumn,
COL_NAME(rkeyid, rkey) AS ReferenceKeyColumn,
constid AS ConstraintID,
OBJECT_SCHEMA_NAME(fkeyid) + '.' + OBJECT_NAME(fkeyid) AS ForeignKeyTable,
fkeyid AS ForeignKeyID,
OBJECT_SCHEMA_NAME(rkeyid) + '.' + OBJECT_NAME(rkeyid) AS ReferenceKeyTable,
rkeyid AS ReferenceKeyID,
keyno AS KeySequenceNumber
FROM
sysforeignkeys
ORDER BY
OBJECT_NAME(rkeyid) ASC, COL_NAME(rkeyid, rkey)
GO
references_run.sql:
references_run.sql:
IF OBJECT_ID('tempdb..#FK')>0
DROP TABLE #FK
IF OBJECT_ID('tempdb..#Const')>0
DROP TABLE #Const
CREATE TABLE #FK
(
ConstraintName VARCHAR(255),
ForeignKeyObject VARCHAR(255),
ReferenceObject VARCHAR(255),
ForeignKeyColumn VARCHAR(255),
ReferenceKeyColumn VARCHAR(255),
ConstraintID INT,
ForeignKeyTable VARCHAR(255),
ForeignKeyID INT,
ReferenceKeyTable VARCHAR(255),
ReferenceKeyID INT,
KeySequenceNumber SMALLINT
)
CREATE TABLE #Const
(
ConstraintID INT,
FBuildField VARCHAR(2000) DEFAULT(''),
RBuildField VARCHAR(2000) DEFAULT(''),
CountField SMALLINT
)
INSERT INTO #FK
EXEC sp_Showforeignkeyobjects
SET NOCOUNT ON
DECLARE tempcursor
CURSOR
READ_ONLY
FOR
SELECT
f.ConstraintName ,
f.ForeignKeyObject ,
f.ReferenceObject ,
f.ForeignKeyColumn ,
f.ReferenceKeyColumn,
f.ConstraintID ,
f.ForeignKeyID ,
f.ReferenceKeyID ,
f.KeySequenceNumber,
f.ForeignKeyTable,
f.ReferenceKeyTable
FROM #FK AS f
INNER JOIN
(
SELECT ConstraintID, MAX(KeySequenceNumber) AS MaxSeq
FROM #FK AS k
GROUP BY k.ConstraintID
)b ON f.ConstraintID = b.ConstraintID AND f.KeySequenceNumber = b.MaxSeq
DECLARE
@ConstraintName VARCHAR(255),
@ForeignKeyObject VARCHAR(255),
@ReferenceObject VARCHAR(255),
@ForeignKeyColumn VARCHAR(255),
@ReferenceKeyColumn VARCHAR(255),
@ConstraintID INT,
@ForeignKeyID INT,
@ReferenceKeyID INT,
@KeySequenceNumber SMALLINT,
@ForeignKeyTable VARCHAR(255),
@ReferenceKeyTable VARCHAR(255)
OPEN tempcursor
FETCH NEXT FROM tempCursor INTO
@ConstraintName ,
@ForeignKeyObject ,
@ReferenceObject ,
@ForeignKeyColumn ,
@ReferenceKeyColumn,
@ConstraintID ,
@ForeignKeyID ,
@ReferenceKeyID,
@KeySequenceNumber,
@ForeignKeyTable ,
@ReferenceKeyTable
WHILE (@@fetch_status <> -1)
BEGIN
DECLARE tempcursor2
CURSOR
READ_ONLY
FOR
SELECT ConstraintID, ForeignKeyColumn, ReferenceKeyColumn, KeySequenceNumber
FROM #FK
WHERE ConstraintID = @ConstraintID
ORDER BY ConstraintID, KeySequenceNumber ASC
DECLARE @ConstraintID2 INT, @ForeignKeyColumn2 VARCHAR(255), @ReferenceKeyColumn2 VARCHAR(255), @KeySequenceNumber2 SMALLINT
DECLARE @FKeyBuildField VARCHAR(1000), @RKeyBuildField VARCHAR(1000), @Cnt SMALLINT
OPEN tempcursor2
SELECT @FKeyBuildField = '', @RKeyBuildField = '', @Cnt = 0
FETCH NEXT FROM tempcursor2 INTO @ConstraintID2 , @ForeignKeyColumn2 , @ReferenceKeyColumn2 , @KeySequenceNumber2
WHILE (@@fetch_status <> -1)
BEGIN
SET @Cnt = @Cnt + 1
SELECT @FKeyBuildField = @FKeyBuildField + ISNULL(@ForeignKeyColumn2,'')+
CASE
WHEN @ForeignKeyColumn2 IS NULL THEN ''
ELSE
CASE WHEN @KeySequenceNumber = @KeySequenceNumber2 THEN '' ELSE ',' END
END
SELECT @RKeyBuildField = @RKeyBuildField + ISNULL(@ReferenceKeyColumn2,'')+
CASE
WHEN @ReferenceKeyColumn2 IS NULL THEN ''
ELSE
CASE WHEN @KeySequenceNumber = @KeySequenceNumber2 THEN '' ELSE ',' END
END
INSERT INTO #Const
(
ConstraintID ,
FBuildField ,
RBuildField,
CountField
)
VALUES
(
@ConstraintID,
@FKeyBuildField,
@RKeyBuildField,
@Cnt
)
FETCH NEXT FROM tempcursor2 INTO @ConstraintID2 , @ForeignKeyColumn2 , @ReferenceKeyColumn2 , @KeySequenceNumber2
END
CLOSE tempcursor2
DEALLOCATE tempcursor2
FETCH NEXT FROM tempCursor INTO
@ConstraintName ,
@ForeignKeyObject ,
@ReferenceObject ,
@ForeignKeyColumn ,
@ReferenceKeyColumn,
@ConstraintID ,
@ForeignKeyID ,
@ReferenceKeyID ,
@KeySequenceNumber ,
@ForeignKeyTable ,
@ReferenceKeyTable
END
CLOSE tempcursor
DEALLOCATE tempcursor
SELECT 'ALTER TABLE ' + FKTable + ' DROP CONSTRAINT ' + OBJECT_NAME(a.ConstraintID) AS DropKeys,
'ALTER TABLE ' + FKTable + ' WITH NOCHECK ADD CONSTRAINT ' + OBJECT_NAME(a.ConstraintID) + ' FOREIGN KEY(' + FBuildField + ') REFERENCES ' + RKTable + '(' + RBuildField+')' AS BuildKeys
,*
FROM #Const a
JOIN
(
SELECT ConstraintID, MAX(countfield) AS maxcount
FROM #Const
GROUP BY ConstraintID
) b ON a.ConstraintID = b.ConstraintID AND a.countfield = b.maxcount
JOIN
(
SELECT DISTINCT constraintid, '[' + OBJECT_SCHEMA_NAME(foreignkeyid) + '].[' + OBJECT_NAME(foreignkeyid) + ']' AS FKTable, '[' + OBJECT_SCHEMA_NAME(referencekeyid) + '].[' + OBJECT_NAME(referencekeyid) + ']' AS RKTable FROM #fk
) c ON a.constraintid = c.constraintid
DROP TABLE #Const
这篇关于NOCHECK 不会禁用外键引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
