Could not drop object because it is referenced by a FOREIGN KEY constraint - SQL Server / TSQL Tutorial Part 74

Scenario:

You are working as SQL Server DBA or Developer, You need to drop a table from a database. When you execute drop table SchemaName.TableName statement, you get below error.

Msg 3726, Level 16, State 1, Line 12
Could not drop object 'SchemaName.TableName' because it is referenced by a FOREIGN KEY constraint.

Now we know that the table is referenced by Foreign Key Constraint. The problem is how to find which table has that Foreign Key Constraint that is referencing to this table.

Solution:

1) There are many ways to get this information. We can use system views to find the name of table which has the Foreign Key Constraint which is referencing our Primary Table.

SELECT Schema_Name(Schema_id) as TableSchemaName,
  object_name(FK.parent_object_id) ParentTableName,
  object_name(FK.referenced_object_id) ReferenceTableName
       FROM sys.foreign_keys AS FK
       WHERE object_name(FK.referenced_object_id)='YourTableName'
       and Schema_Name(Schema_id)='YourTableSchemaName'


I executed above script for my customer table which is present in dbo schema and here is what I got.
How to find which table's Foreign Key is referencing to Table in SQL Server.


Now we know that Ord is the table which has the Foreign Key Constraint which is referencing our table. We can go ahead and drop the foreign key Constraint and then drop our table.

2) Use System Stored Procedure sp_fkeys

We can use system Stored Procedure to get the Foreign Key Constraints information which are referencing to our table. If my table name is Customer, I can run script as below

EXEC sp_fkeys 'Customer'

How to get Foreign Key Constraint name for a Table in SQL Server

The sp_fkeys returns very detailed information, few of the columns are not shown in snapshot above. here is the list of columns it will return.


  • PKTABLE_QUALIFIER
  • PKTABLE_OWNER
  • PKTABLE_NAME
  • PKCOLUMN_NAME
  • FKTABLE_QUALIFIER
  • FKTABLE_OWNER
  • FKTABLE_NAME
  • FKCOLUMN_NAME
  • KEY_SEQ
  • UPDATE_RULE
  • DELETE_RULE
  • FK_NAME
  • PK_NAME


Video Demo : Could not drop object because it is referenced by a Foreign Key Constraint



No comments:

Post a Comment