SQL删除表外键约束

Question

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

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]

- user188229

---

请查看此链接：http://ysgitdiary.blogspot.com/2010/03/how-to-remove-drop-foreign-keys-in-sql.html - Yordan Georgiev

看看这个答案，它帮了我;) https://dev59.com/fG445IYBdhLWcg3wcJ6O#5488670 - xatz

Answer 1

不会的，如果确实有引用它的外键，这不会删除你的表。

要获取所有引用您的表的外键关系，可以使用此SQL（如果您使用的是SQL Server 2005及以上版本）：

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

如果有外键关系存在，通过这个语句，你可以创建SQL语句来删除这些外键关系：

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')

- marc_s

---

注意：生成的表包含删除单个约束的语句，需要在不同的查询中运行。在我的情况下，它非常有效，因为我需要摆脱一个无用的表（用于测试目的），同时保持其余具有外键关系的表完好无损。 - Mauricio Quintana

我不得不使用parent_object_id而不是referenced_object_id。 - Tom Robinson

获取所有引用表的SELECT语句的替代方案是：EXEC sp_fkeys 'Student'; - Buggieboy

对此查询进行小调整：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')将 [name] 替换为约束名 CONSTRAINT name。 - Stas Svishov

如何将您最后选择的内容分配为变量并执行它？ - Hrvoje T

Answer 2

在 SQL Server Management Studio 2008 (R2) 和更新版本中，你可以右键点击 DB -> Tasks -> Generate Scripts，选择要删除的表，选择 "Save to new query window"，单击 Advanced 按钮，将 Script DROP and CREATE 设置为 Script DROP，将 Script Foreign Keys 设置为 True, 然后单击 OK。接着单击 Next -> Next -> Finish，查看脚本并执行。

- Riaan

---

仍然无法删除'my_table'对象，因为它被一个外键约束所引用。 - FrenkyB

这个答案为什么有28个赞我不理解... 伙计，它还是不起作用。 - AltF4_

虽然它生成了脚本，但是该脚本并未解决问题，即删除外键约束引用的表。 - Alexey Shevelyov

SQL 2019非常好用，具有外键约束，没有任何问题就通过了它们。 - littlevahn

SSMS 18.12.1非常好用！有很多外键约束，但它完美地工作了！干杯。 - scott_f

我点赞了这个，觉得听起来很棒。但当我尝试之后，发现它并没有什么用处，可是我已经无法取消我的点赞了。我没有看到任何"高级按钮"或者"将脚本外键设置为True"的选项。我使用的是SQL Server 12版本。 - egmfrs

Answer 3

如果您先删除“child”表，那么外键也将被删除。如果您尝试先删除“parent”表，则会出现“无法删除对象'a'，因为它受到FOREIGN KEY约束的引用。”的错误提示。

- Philip Kelley

---

没错，但没有解决方案。子表可能会有其他表的外键引用，而且你可能不想首先删除它。 - MaR

如果我的目标是“删除数据库中的所有表格”，那么正确的解决方案是真正的。 - Philip Kelley

考虑到提问者想要“删除数据库中的所有表”，这个回答比被采纳的回答好得多。 - lukkea

Answer 4

这里是另一种正确删除所有表的方法，使用 sp_MSdropconstraints 存储过程。我能想到的最短代码：

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

- Krait

---

这个简单的两行代码真正回答了原问题“如果我想删除数据库中的所有表”。至少对于我的SQLServer 2019是如此。谢谢。 - Dominik

Answer 5

稍微通用一点的版本，与@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')

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

- Stas Svishov

---

嗨，我一直在尝试通过上述方法删除我的外键约束，但是当我之后去删除一个表时，它一直显示“无法删除该对象，因为它仍被外键约束引用...有什么想法吗？提前感谢。 - daniness

Answer 6

一切都变得简单多了。有一个配置可以关闭检查和打开检查。

例如，如果您使用的是MySQL，则要关闭它，必须编写SET foreign_key_checks = 0;

然后删除或清除表，重新启用检查 SET foreign_key_checks = 1;

- Фарид

Answer 7

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.

- CodeTzu

Answer 8

这里是一个完整的脚本，用于实现一种解决方案：

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

- Roman Pokrovskij

Answer 9

这是另一种删除所有约束再删除表本身的方法，使用涉及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)

- Warren Rumak

---

这不太好用，因为并非所有的FK都会被删除（只有那些我们的表作为父表使用的FK会被删除，而我们还应该删除我们的表被用作“引用”的约束）。 - Roman Pokrovskij

Roman，原帖是关于删除外键约束的。我的回答不旨在涵盖除此以外的任何内容。 - Warren Rumak

Answer 10

如果是 SQL Server，您必须在删除表之前删除约束。

- Shiraz Bhaiji