禁用外键约束,仍然不能截断表? (SQL Server 2005)

nquas 发布于 2018-10-11 constraints 最后更新 2018-10-11 22:59 17 浏览

我有一个名为PX_Child的表,它在PX_Parent上有一个外键。我想暂时禁用此FK约束,以便我可以截断PX_Parent。但我不确定这是怎么回事。 我试过这些命令

ALTER TABLE PX_Child NOCHECK CONSTRAINT ALL
ALTER TABLE PX_Parent NOCHECK CONSTRAINT ALL
(truncate commands)
ALTER TABLE PX_Child CHECK CONSTRAINT ALL
ALTER TABLE PX_Parent CHECK CONSTRAINT ALL
但截断仍然告诉我,由于外键约束,它不能截断PX_Parent。我查看了所有网络,似乎无法找到我做错了什么,对于这个问题的基本性质感到抱歉。
已邀请:

wiusto

赞同来自:

有一种更容易的方式。我遇到了同样的问题并找到了这个解决方案: https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/ 如果您只是在数据库中运行此查询,它将生成您需要在您的sproc之前/之后包含的T-SQL,以便删除然后恢复任何外键约束。 不要担心尝试理解这个查询本身。

CREATE TABLE #x -- feel free to use a permanent table
(
  drop_script NVARCHAR(MAX),
  create_script NVARCHAR(MAX)
);
DECLARE @drop   NVARCHAR(MAX) = N'',
        @create NVARCHAR(MAX) = N'';
-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id];
INSERT #x(drop_script) SELECT @drop;
-- create is a little more complex. We need to generate the list of 
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += N'
ALTER TABLE ' 
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
UPDATE #x SET create_script = @create;
PRINT @drop;
PRINT @create;
/*
EXEC sp_executesql @drop
-- clear out data etc. here
EXEC sp_executesql @create;
*/
生成一堆:
ALTER TABLE [dbo].[Whatever] DROP CONSTRAINT....
--
ALTER TABLE [dbo].[Whatever] ADD CONSTRAINT....

jfugit

赞同来自:

如果有任何外键引用它,则不能截断表,包括禁用的约束。您需要删除外键约束或使用DELETE命令。

trerum

赞同来自:

SQL服务器不允许您在约束存在时截断表,即使它已被禁用。 删除约束并在截断表后重新创建约束。 或者只需删除并重新创建表格,无论哪种更容易在您的应用程序中执行。

要回复问题请先登录注册