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

Question

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.

Using SQL Server 2012

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

Answer 1

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

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

This will give you all the tables where 'Table1' primary key is a 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.

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/

Answer 2

Firstly, you need to drop your 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

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

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

Answer 3

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.

Answer 4

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.

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 

Answer 5

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:

EXEC sp_fkeys 'Table_Name'

and then run the drop command which is as follows:

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.

DROP TABLE dbo.Table_Name

Answer 6

Using...

Microsoft SQL Server Management Studio (v. 18.11.1)

If you try to just drop the problem table, you'll get this error:

For being able to drop this table, as suggested by @rjso in his answer, run a new query with the following command to get the first hint of where's the current problem coming from:

EXEC sp_fkeys 'Table1'
/* In this example the problem is in the table "Titular" */

This will tell you where the problem actually is located:

In this example the problem table is Titular, and the wronged reference points to the table Expediente, in its row id_persona. Let's go there:

Right click on id_persona:

Then delete the relationship:

This will allow you now to drop the table (in this case, Titular table).

PS. I forgot to screenshot it when I dropped it in my database. This solution worked.