SQL Server will not allow you to drop table without dropping foreign key constraint, foreign key means column of one table refers primary key of another table. This relationship called a Foreign key relationship, here primary table will act as parent table and secondary table called as child table.
If you try to drop primary table (Parent table) without dropping foreign key from secondary table (Child table) it will throw error like “Could not drop object ‘sfUsers’ because it is referenced by a FOREIGN KEY constraint”
If you drop the “child” table first, it will drop foreign key automatically.
I am trying to drop table that was referred by another table as a foreign key constraint.
DROP TABLE sfusers
Msg 3726, Level 16, State 1, Line 1
Could not drop object ‘sfUsers’ because it is referenced by a FOREIGN KEY constraint.
To see the list of foreign keys associated with table, use execute below query.
SELECT * FROM sys.foreign_keys WHERE referenced_object_id = object_id(‘sfusers’)
To fix this issue, drop the foreign key constraint first. Don’t forget to take backup of Foreign key using script.
Click on plus sign to expand database -> Select table folder-> Keys -> Right click on foreign key and delete.
You also use below statement to drop foreign key in sql server
ALTER TABLE dbo.[ForeignKeytable] DROP CONSTRAINT fk_user_001
Option 2 :
Another option is using sql server management studio and generate script.
Right Click on the database -> Tasks -> Generate Scripts.
1. Select the tables you want to DROP.
2. Select “Save to new query window”.
3. Click on the Advanced button.
4. Set “Script DROP and CREATE” -> Script DROP.
5. Set Script Foreign Keys to True.
6. Click OK.
7. Click Next -> Next -> Finish.
8. View the script and then Execute.