SQL 无法删除对象“dbo.Table1”，因为它被外键约束引用

Could not drop object 'dbo.Table1' because it is referenced by a FOREIGN KEY constraint

提问by Mathematics

Even though I am removing and trying to drop table, I get error,

即使我正在删除并尝试删除表，我也会出错，

ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [FK_Table1_Table2]
GO

DROP TABLE [dbo].[Table1]
GO

Error

错误

Msg 3726, Level 16, State 1, Line 2 Could not drop object 'dbo.Table1' because it is referenced by a FOREIGN KEY constraint.

消息 3726，级别 16，状态 1，第 2 行无法删除对象“dbo.Table1”，因为它被外键约束引用。

Using SQL Server 2012

使用 SQL Server 2012

I generated the script using sql server 2012, so did sQL server gave me wrong script ?

我使用 sql server 2012 生成了脚本，所以 sql server 给了我错误的脚本吗？

回答by rjso

Not sure if I understood correctly what you are trying to do, most likely Table1 is referenced as a FK in another table.

不确定我是否正确理解您要执行的操作，很可能 Table1 在另一个表中被引用为 FK。

If you do:

如果你这样做：

EXEC sp_fkeys 'Table1'

(this was taken from How can I list all foreign keys referencing a given table in SQL Server?)

（这取自如何列出引用 SQL Server 中给定表的所有外键？）

This will give you all the tables where 'Table1' primary key is a FK.

这将为您提供“Table1”主键是 FK 的所有表。

Deleting the constraints that exist inside a table its not a necessary step in order to drop the table itself. Deleting every possible FK's that reference 'Table1' is.

删除表中存在的约束不是删除表本身的必要步骤。删除引用“Table1”的所有可能的 FK。

As for the the second part of your question, the SQL Server automatic scripts are blind in many ways. Most likely the table that is preventing you to delete Table1, is being dropped below or not changed by the script at all. RedGate has a few tools that help with those cascading deletes (normally when you are trying to drop a bunch of tables), but its not bulletproof and its quite pricey. http://www.red-gate.com/products/sql-development/sql-toolbelt/

至于你问题的第二部分，SQL Server 自动脚本在很多方面都是盲目的。很可能阻止您删除 Table1 的表被删除或根本没有被脚本更改。RedGate 有一些工具可以帮助处理这些级联删除（通常是当你试图删除一堆表时），但它不是防弹的，而且价格昂贵。http://www.red-gate.com/products/sql-development/sql-toolbelt/

回答by Orlando Herrera

Firstly, you need to drop your FK.

首先，你需要放弃你的FK。

I can recommend you take a look in this stack overflow post, is very interesting. It is called: SQL DROP TABLE foreign key constraint

我可以推荐你看看这个堆栈溢出帖子，非常有趣。叫做：SQL DROP TABLE外键约束

There are a good explanation about how to do this process.

关于如何执行此过程有很好的解释。

I will quote a response:

我将引用一个回应：

.....Will not drop your table if there are indeed foreign keys referencing it.

To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):

.....如果确实有外键引用它，则不会删除您的表。

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

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


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

回答by Tab Alleman

You need to drop the FK on Table it has been added to, now this can be Table2, Table3 or whatever table, which references Table1's column as Foreign Key. Then you can drop Table1.

您需要将 FK 放在它已添加到的表上，现在这可以是 Table2、Table3 或任何将 Table1 的列作为外键引用的表。然后你可以删除Table1。

回答by Ardalan Shahgholi

You can use those queries to find all FK in your table and Find the FKs in the tables in which your table is used.

您可以使用这些查询来查找表中的所有 FK，并在使用您的表的表中查找 FK。

Declare @SchemaName VarChar(200) = 'Your Schema name'
Declare @TableName VarChar(200) = 'Your Table Name'

-- Find FK in This table.
SELECT 
    ' IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = 
OBJECT_ID(N''' + 
      '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' + 
      '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + 
OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +

    'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(FK.parent_object_id) +
    '.[' + OBJECT_NAME(FK.parent_object_id) + 
    '] DROP CONSTRAINT ' + FK.name
    , S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O 
  ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S 
  ON (O.schema_id = S.schema_id)  
WHERE 
      O.name = @TableName
      And S.name = @SchemaName


-- Find the FKs in the tables in which this table is used
  SELECT 
    ' IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =   
      OBJECT_ID(N''' + 
      '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
  + ''') AND parent_object_id = OBJECT_ID(N''' + 
      '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + 
 OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +

    ' ALTER TABLE ' +  OBJECT_SCHEMA_NAME(FK.parent_object_id) +
    '.[' + OBJECT_NAME(FK.parent_object_id) + 
    '] DROP CONSTRAINT ' + FK.name
    , S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O 
  ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S 
  ON (O.schema_id = S.schema_id)  
WHERE 
      O.name = @TableName
      And S.name = @SchemaName 

回答by Tahir77667

This happens because the table you are trying to alter has a primary key(PK) which is referenced as a foreign key(FK) somewhere in a different table.To know which table is it, execute the below stored procedure:

发生这种情况是因为您尝试更改的表有一个主键 (PK)，它在不同表的某处被引用为外键 (FK)。要知道它是哪个表，请执行以下存储过程：

EXEC sp_fkeys 'Table_Name'

EXEC sp_fkeys 'Table_Name'

and then run the drop command which is as follows:

然后运行 ​​drop 命令，如下所示：

DROP TABLE Table_Name

DROP TABLE Table_Name

Note:'dbo.' is a default system derived schema, therefore you need not have to mention that in the command as follows.Don't worry even if you mention the schema it'll work.

注：“dbo”。是默认的系统派生架构，因此您不必在命令中提及它，如下所示。即使提及该架构也不必担心，它会起作用。

DROP TABLE dbo.Table_Name

DROP TABLE dbo.Table_Name