如何在所有表中删除所有外键约束?
如何在所有表中删除所有外键约束?
我想编写SQL命令来删除所有表中的约束。我在互联网上搜索到以下内容,如果数据库很小且不复杂,它可以正常工作。
声明变量@name VARCHAR(128)
声明变量@constraint VARCHAR(254)
声明变量@SQL VARCHAR(254)
声明变量@schema VARCHAR(128)
选择@name = (从INFORMATION_SCHEMA.TABLE_CONSTRAINTS中选择TABLE_NAME的前1个结果,其中constraint_catalog=DB_NAME()且CONSTRAINT_TYPE = 'FOREIGN KEY',按TABLE_NAME排序)
选择@schema = (从sys.objects中选择[name] = @name的前1个结果的schema_name(schema_id))
当@name不为空时
选择@constraint = (从INFORMATION_SCHEMA.TABLE_CONSTRAINTS中选择TABLE_NAME = @name的前1个结果的CONSTRAINT_NAME,其中constraint_catalog=DB_NAME()且CONSTRAINT_TYPE = 'FOREIGN KEY',按CONSTRAINT_NAME排序)
当@constraint不为空时
选择@SQL = 'ALTER TABLE ' + @schema + '.[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
执行(@SQL)
打印'Dropped FK Constraint: ' + @constraint + ' on ' + @name
选择@constraint = (从INFORMATION_SCHEMA.TABLE_CONSTRAINTS中选择TABLE_NAME = @name且CONSTRAINT_NAME <> @constraint的前1个结果的CONSTRAINT_NAME,其中constraint_catalog=DB_NAME()且CONSTRAINT_TYPE = 'FOREIGN KEY',按CONSTRAINT_NAME排序)
选择@name = (从INFORMATION_SCHEMA.TABLE_CONSTRAINTS中选择TABLE_NAME的前1个结果,其中constraint_catalog=DB_NAME()且CONSTRAINT_TYPE = 'FOREIGN KEY',按TABLE_NAME排序)
选择@schema = (从sys.objects中选择[name] = @name的前1个结果的schema_name(schema_id))
结束
GO
但是,如果我在更复杂的数据库或AdventureWork中运行它,则无法正常工作。它会显示一些错误,如下所示。
消息 3728,级别 16,状态 1,行 1
'FK_ap_invoice_modification_type_id'不是一个约束。
消息 3727,级别 16,状态 0,行 1
无法删除约束。请参阅前面的错误。
消息 3725,级别 16,状态 0,行 1
'PK_ap_invoice'约束正在被表'_drop_now_ap_invoice_detail'引用,外键约束为'FK_ap_invoice_detail_ap_invoice'。
消息 3727,级别 16,状态 0,行 1
无法删除约束。请参阅前面的错误。
原因是某些外键被其他表引用。我必须运行此脚本几次,直到数据库清除为止。
我想知道如何清除数据库中的所有外键。
在使用数据库时,有时候需要删除所有表中的外键约束。然而,当数据库中的外键约束数量较多时,使用传统的逐个删除的方法效率很低。因此,我们需要一种能够一次性删除所有外键约束的方法。
下面是两个改进的脚本,它们可以解决当需要删除的外键约束数量超过SQL
变量(4000
或MAX
字符)限制时的问题。
第一个脚本由 提供,第二个脚本由 提供。这两个脚本每次迭代会删除5
个外键约束(为了安全起见,通过添加TOP 5
实现)。当没有外键约束可删除时,脚本会停止执行(运行SELECT
后,SQL
变量保持为空)。
第一个脚本:
DECLARE @SQL varchar(4000)
IterationStart:
SET @SQL =''
SELECT TOP 5 @SQL = @SQL + 'ALTER TABLE ' + FK.TABLE_NAME + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
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
IF @SQL <> ''
BEGIN
EXEC(@SQL)
GOTO IterationStart
END
第二个脚本:
DECLARE @SQL nvarchar(MAX)
IterationStart:
SET @SQL = ''
SELECT TOP 5 @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(RC.CONSTRAINT_SCHEMA)
+ '.' + QUOTENAME(KCU1.TABLE_NAME)
+ ' DROP CONSTRAINT ' + QUOTENAME(rc.CONSTRAINT_NAME) + '; '
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
IF @SQL <> ''
BEGIN
EXEC(@SQL)
GOTO IterationStart
END
使用 提供的脚本,可以完美地解决这个问题。赞赏!
以上就是如何删除所有表中外键约束的方法。这些改进的脚本能够高效地一次性删除所有外键约束,提高了数据库操作的效率。无论数据库中外键约束的数量多少,都可以轻松地使用这些脚本来处理。希望这篇文章对你有所帮助!
如何在所有表中删除所有外键约束?
有很多关于这个主题的信息。查看这个详细回答,它讨论了临时禁用外键,但是阅读并根据需要修改后,您将得到一个很好的可以使用并实现很多功能的脚本。
我可以提供两个不同的脚本来获取所有外键。在这两种情况下,请取消注释--EXEC ()
以执行您的ALTER
代码。或者您可以等待它打印出所有的更改语句,然后复制粘贴以执行它们。
第一个脚本使用INFORMATION_SCHEMA
来获取约束:
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 () PRINT @sql
第二个脚本使用不同的系统视图和CTE表。
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 () PRINT @sql
您应该使用varchar(max)
和'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) + '
来避免语法错误。
在SQL Server 2008及更高版本中,可以使用以下简短而简洁的脚本来删除所有外键,并考虑对象的架构:
declare @sql nvarchar(max) = ( select 'alter table ' + quotename(schema_name(schema_id)) + '.' + quotename(object_name(parent_object_id)) + ' drop constraint '+quotename(name) + ';' from sys.foreign_keys for xml path('') ); exec sp_executesql @sql;
这个方法非常有效。需要注意的是,在SQL Server Express Edition中,需要将参数定义为`nvarchar`,使用`varchar`会抛出以下错误:`Procedure expects parameter '' of type 'ntext/nchar/nvarchar'."` 这在一些最新的SQL Server版本中也可能是正确的,但我无法进行测试。
文章完整内容如下:
如何删除所有表中的所有外键约束?
在SQL Server中,外键约束用于确保表之间的关系完整性。但是,在某些情况下,您可能需要删除所有表中的所有外键约束。本文将介绍如何使用简洁的脚本来实现这一目标。
以下是一个可在SQL Server 2008及更高版本中使用的简单脚本,可以删除所有外键,同时考虑到对象的架构:
declare @sql nvarchar(max) = ( select 'alter table ' + quotename(schema_name(schema_id)) + '.' + quotename(object_name(parent_object_id)) + ' drop constraint '+quotename(name) + ';' from sys.foreign_keys for xml path('') ); exec sp_executesql @sql;
这个脚本使用了系统视图`sys.foreign_keys`来获取所有外键的详细信息,并生成相应的`alter table`语句来删除外键。
需要注意的是,在SQL Server Express Edition中,必须将参数定义为`nvarchar`,否则会抛出错误。这个错误信息是:“Procedure expects parameter '' of type 'ntext/nchar/nvarchar'.”
使用上述脚本,您可以轻松地删除所有表中的所有外键约束。这在某些场景下可能非常有用,比如在重构数据库结构或进行大规模数据迁移时。
本文介绍了如何使用简洁的脚本删除所有表中的所有外键约束。通过使用`sys.foreign_keys`系统视图和动态SQL语句,我们可以有效地删除所有外键。希望本文对您理解如何处理外键约束问题有所帮助。