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

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

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 ?

Solution

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/