TABLE 外键约束

如果我想像这样删除数据库中的所有表，它会处理外键约束吗？如果没有，我该怎么做？

GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
DROP TABLE dbo.[Student]
426637 次浏览
小开

如果是 SQLServer，则必须先删除约束，然后才能删除表。

小开

如果您首先删除“子”表，那么外键也将被删除。如果您尝试先删除“父”表，您将得到一个“无法删除对象‘ a’，因为它被 FOREIGNKEY 约束引用”错误。

小开
最佳答案

不，如果确实存在引用表的外键，那么它不会丢弃表。

要获得引用表的所有外键关系，可以使用这个 SQL (如果使用的是 SQLServer2005或更高版本) :

SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

如果有的话，通过这个语句，你可以创建 SQL 语句去掉那些 FK 关系:

SELECT
'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
'].[' + OBJECT_NAME(parent_object_id) +
'] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')
小开

如果我想删除 我的数据库

那么删除整个数据库就容易多了:

DROP DATABASE WorkerPensions
小开

在 SQLServerManagementStudio2008(R2)和更新版本中，可以右击

DB-> 任务-> 生成脚本

  • 选择要删除的表。

  • 选择“保存到新查询窗口”。

  • 点击高级按钮。

  • 将脚本 DROP 和 CREATE 设置为脚本 DROP。

  • 将脚本外键设置为 True。

  • 单击 OK。

  • 单击 Next-> Next-> Finish。

  • 查看脚本，然后执行。

小开

下面是另一种删除表本身所遵循的所有约束的方法，它使用了一种涉及 FOR XML PATH('')的连接技巧，该技巧允许将多个输入行合并到单个输出行中。应该在任何 SQL 2005和更高版本上工作。

为了安全起见，我把 EXECUTE 命令注释掉了。

DECLARE @SQL NVARCHAR(max)
;WITH fkeys AS (
SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname
FROM sys.foreign_keys fk
JOIN sys.objects o ON fk.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
)
SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
FROM fkeys
FOR XML PATH('')),1,2,'')


-- EXECUTE(@sql)


SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
FOR XML PATH('')),1,2,'')


-- EXECUTE(@sql)
小开

下面是实现解决方案的完整脚本:

create Procedure [dev].DeleteTablesFromSchema
(
@schemaName varchar(500)
)
As
begin
declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128),  @constraintName nvarchar(128)
declare @sql nvarchar(max)
-- delete FK first
declare cur1 cursor for
select distinct
CASE WHEN t2.[object_id] is NOT NULL  THEN  s2.name ELSE s.name END as SchemaName,
CASE WHEN t2.[object_id] is NOT NULL  THEN  t2.name ELSE t.name END as TableName,
CASE WHEN t2.[object_id] is NOT NULL  THEN  OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
from sys.objects t
inner join sys.schemas s
on t.[schema_id] = s.[schema_id]
left join sys.foreign_key_columns d
on  d.parent_object_id = t.[object_id]
left join sys.foreign_key_columns d2
on  d2.referenced_object_id = t.[object_id]
inner join sys.objects t2
on  d2.parent_object_id = t2.[object_id]
inner join sys.schemas s2
on  t2.[schema_id] = s2.[schema_id]
WHERE t.[type]='U'
AND t2.[type]='U'
AND t.is_ms_shipped = 0
AND t2.is_ms_shipped = 0
AND s.Name=@schemaName
open cur1
fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
while @@fetch_status = 0
BEGIN
set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
exec(@sql)
fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
END
close cur1
deallocate cur1


DECLARE @tableName nvarchar(128)
declare cur2 cursor for
select s.Name, p.Name
from sys.objects p
INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
WHERE p.[type]='U' and is_ms_shipped = 0
AND s.Name=@schemaName
ORDER BY s.Name, p.Name
open cur2


fetch next from cur2 into @schemaName,@tableName
while @@fetch_status = 0
begin
set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
exec(@sql)
fetch next from cur2 into @schemaName,@tableName
end


close cur2
deallocate cur2


end
go
小开

下面是另一种正确删除所有表的方法，使用 sp_MSdropconstraints过程。我能想到的最短的代码:

exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
exec sp_MSforeachtable "drop table ?";
小开

@ mark _ s 发布的内容稍微更通用一些，这对我有所帮助

SELECT
'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(k.parent_object_id) +
'.[' + OBJECT_NAME(k.parent_object_id) +
'] DROP CONSTRAINT ' + k.name
FROM sys.foreign_keys k
WHERE referenced_object_id = object_id('your table')

只需插入表名，然后执行结果。

小开

如果您希望 DROP表已被其他使用外键的表引用，请使用

DROP TABLE *table_name* CASCADE CONSTRAINTS;
我觉得应该对你有用。

小开

如果您使用的是 天啊sql 服务器(不是多发性硬化症SQL) ，并且不介意丢失表，那么可以使用一个简单的查询来一次删除多个表:

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
SET foreign_key_checks = 1;

这样，在查询中以什么顺序使用表并不重要。

如果有人要说这不是一个好的解决方案，如果你有一个包含很多表的数据库: 我同意！

小开 
Removing Referenced FOREIGN KEY Constraints
Assuming there is a parent and child table Relationship in SQL Server:


--First find the name of the Foreign Key Constraint:
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('States')


--Then Find foreign keys referencing to dbo.Parent(States) table:
SELECT name AS 'Foreign Key Constraint Name',
OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
OBJECT_NAME(referenced_object_id) = 'dbo.State'


-- Drop the foreign key constraint by its name
ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;


-- You can also use the following T-SQL script to automatically find
--and drop all foreign key constraints referencing to the specified parent
-- table:


BEGIN


DECLARE @stmt VARCHAR(300);


-- Cursor to generate ALTER TABLE DROP CONSTRAINT statements
DECLARE cur CURSOR FOR
SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' +
OBJECT_NAME(parent_object_id) +
' DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND
OBJECT_NAME(referenced_object_id) = 'states';


OPEN cur;
FETCH cur INTO @stmt;


-- Drop each found foreign key constraint
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@stmt);
FETCH cur INTO @stmt;
END


CLOSE cur;
DEALLOCATE cur;


END
GO


--Now you can drop the parent table:


DROP TABLE states;
--# Command(s) completed successfully.
小开

使用 SQLServer 管理器可以从 UI 中删除外键约束。如果希望删除表 Diary，但 User 表有一个指向 Diary表的外键 DiaryId，那么可以展开(使用加号) User表，然后展开 Foreign Keys部分。右键单击指向日记表的外键，然后选择 Delete。然后您可以展开 Columns部分，右键单击并删除列 DiaryId。然后你就可以跑了:

drop table Diary

我知道你的实际问题是关于删除所有的表，所以这可能不是一个有用的情况下。但是，如果你只是想删除一些表，我相信这是有用的(标题没有明确提到删除所有表)。

小开

执行下面的代码以获取阻止删除的外键约束名称。例如，我采用 roles表。

      SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('roles');


SELECT name AS 'Foreign Key Constraint Name',
OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id)
AS 'Child Table' FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo'
AND OBJECT_NAME(referenced_object_id) = 'dbo.roles'

你会得到 FK 的名字如下: FK__Table1__roleId__1X1H55C1

现在运行下面的代码来删除从上面得到的 FK 参考。

ALTER TABLE dbo.users drop CONSTRAINT FK__Table1__roleId__1X1H55C1;

成交！

小开

一切都很简单。有一个配置关闭检查和打开它。

例如，如果您正在使用 MySQL，那么要关闭它，您必须编写 SET foreign _ key _ check = 0;

然后删除或清除该表，并重新启用检查 SET foreign _ key _ check = 1;

小开

找到所有的外键. . 脚本他们

SELECT *  FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

然后从子表中删除外键。 现在你可以放弃父表了。

如果要重新创建父表，请确保运行前面创建的脚本。

小开

此脚本将允许您使用与约束名称相匹配的字符串删除所有外键约束

    DECLARE @constraintMatchString nvarchar(max) = N'FK_<SOME-MATCH-STRING>%';
DECLARE @sql nvarchar(max) = N'';


;WITH x (schemaAndTableName, constraintName) AS
(
SELECT
schemaAndTableName = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)),
constraintName = name
FROM sys.foreign_keys
where name like @constraintMatchString
group by QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)), name
)
SELECT @sql += N'ALTER TABLE ' + schemaAndTableName + N' DROP CONSTRAINT [' +constraintName +N'];
' FROM x;


EXEC sys.sp_executesql @sql;

只需根据需要替换 FK_<SOME-MATCH-STRING>%值(注意，%是通配符匹配)


提交答案