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