如何删除所有表中的所有外键约束? [英] How to drop all Foreign Key constraints in all tables?
问题描述
我想编写 sql 命令来删除所有表中的所有约束.我在互联网上搜索,发现以下如果数据库小且不复杂,则可以正常工作.
I want to write sql command to drop all constraints in all tables. I searched on the internet and found the following which works fine if the database is small and not complex.
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
DECLARE @schema VARCHAR(128)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
SELECT @schema = (SELECT TOP 1 schema_name(schema_id) FROM sys.objects WHERE [name] = @name)
WHILE @name is not null
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint IS NOT NULL
BEGIN
SELECT @SQL = 'ALTER TABLE ' + @schema + '.[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL)
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
SELECT @schema = (SELECT TOP 1 schema_name(schema_id) FROM sys.objects WHERE [name] = @name)
END
GO
如果我使用更复杂的数据库甚至 AdventureWork 运行它,它就不起作用.它显示了一些错误,如下所示.
It does not work if I run it with a more complex database or even AdventureWork. It shows some erros like below.
Msg 3728, Level 16, State 1, Line 1
'FK_ap_invoice_modification_type_id' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
Msg 3725, Level 16, State 0, Line 1
The constraint 'PK_ap_invoice' is being referenced by table '_drop_now_ap_invoice_detail', foreign key constraint 'FK_ap_invoice_detail_ap_invoice'.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
这是因为一些 FK 被其他表引用了.我必须运行这个脚本几次,直到数据库干净为止.
The reason is because some FKs are referenced by other table. I have to run this script for a couple times until the database is clean.
我想知道如何清除数据库中的所有 FK.
I want to know how can I clear all FKs in the database.
推荐答案
有很多关于这个主题的信息.查看@AaronBertrand 的此详细答案.它谈到暂时禁用外键,但阅读所有内容并随意修改,您将有一个很好的脚本可以玩并取得很多成就.
There is lot of information about the subject all around. Check this detailed answer by @AaronBertrand. It talks about temporary disabling the foreign keys but reading it all and modifying at will you will have a nice script to play with and achieve a lot.
在我看来,我可以提出 2 个不同的脚本来获取所有外键.在这两种情况下,取消注释 --EXEC (@SQL)
以执行您的 ALTER
代码.或者你可以等到它打印所有的alter子句,然后复制粘贴来执行它们.
From my side I can propose 2 different scripts to get all foreign keys. On both cases uncomment the --EXEC (@SQL)
to execute your ALTER
code. Or you can wait until it prints all the alter clauses and then copy paste to execute them.
第一个使用 INFORMATION_SCHEMA
来获取约束:
First one uses the INFORMATION_SCHEMA
to get the constraints:
DECLARE @SQL VARCHAR(MAX)=''
SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
--SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
--EXEC (@SQL)
PRINT @SQL
这个使用不同的系统视图和 CTE 表.
This one using different system views and a CTE table.
DECLARE @SQL varchar(4000)=''
;WITH ReferencingFK AS
(
SELECT fk.Name AS 'FKName', OBJECT_NAME(fk.parent_object_id) 'ParentTable',
cpa.name 'ParentColumnName', OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable',
cref.name 'ReferencedColumnName'
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
INNER JOIN sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id
)
SELECT @SQL = @SQL + 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT [' + RTRIM(FKName) +'];' + CHAR(13)
--SELECT FKName, ParentTable, ParentColumnName, ReferencedTable, ReferencedColumnName
FROM ReferencingFK
WHERE ReferencedTable = 'Employee'
ORDER BY ParentTable, ReferencedTable, FKName
--EXEC (@SQL)
PRINT @SQL
这篇关于如何删除所有表中的所有外键约束?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!