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 ?

Duplicate: http://stackoverflow.com/questions/15107238/how-can-i-drop-a-table-if-there-is-a-foreign-key-constraint-in-sql-server?rq=1 — Tab Alleman, Aug 12 '14 at 15:11
This is a frustrating process as you're no doubt experiencing. Try this: I keep a "dependencies" spreadsheet going as I build my tables that notes the PK created by each table and the FK's it references in other tables. You'll need this info anyway when you map out how a user adds data. For ex, when a user is on a screen and adding a record to Table1, what needs to be added to Table2 and Table3 **prior** to being able to do append to Table1? These rules can be enforced through validation or a warning to the user. Hope this helps. — Tim Bostwick, Aug 13 '14 at 00:12

score 41 · Accepted Answer · edited May 23 '17 at 12:09

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/

score 18 · Answer 2 · edited Jun 20 '20 at 09:12

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

score 3 · Answer 3 · edited Aug 12 '14 at 15:31

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.

edited Aug 12 '14 at 15:31

TylerH

20,799
66
75
101

answered Aug 12 '14 at 14:55

Tab Alleman

31,483
7
36
52

In order to drop a table, you must drop the FK's that exist on OTHER tables that point to the table you want to drop. Management Studio does NOT automatically generate this as part of the DROP script. I did not say that FK_Table1_Table2 belongs to Table 1, I said that you need to drop the FK on Table 2 (whatever it may be called). You can find all the FK's that reference Table 1 by right-clicking the table and looking at "dependencies". – Tab Alleman Aug 12 '14 at 15:04

score 3 · Answer 4 · answered Oct 29 '15 at 23:04

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

score 3 · Answer 5 · answered May 10 '18 at 12:58

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

score 3 · Answer 6 · answered Apr 22 '22 at 12:57

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.

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

6 Answers6

Microsoft SQL Server Management Studio (v. 18.11.1)

Linked