NOCHECK 不会禁用外键引用

本文介绍了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"

取自外键约束如何临时禁用使用 T-SQL?

也试过了

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'，因为它正被一个引用外键约束.

有没有办法禁用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:

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:

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 不会禁用外键引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！