Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL DROP TABLE foreign key constraint

People also ask

Can we DROP TABLE by foreign key constraint?

In SQL Server, you cannot drop a table if it is referenced by a FOREIGN KEY constraint. You have to either drop the child tables before removing the parent table, or remove foreign key constraints.

Does DROP TABLE remove constraints?

DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified.


No, this 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')

and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:

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

In SQL Server Management Studio 2008 (R2) and newer, you can Right Click on the

DB -> Tasks -> Generate Scripts

  • Select the tables you want to DROP.

  • Select "Save to new query window".

  • Click on the Advanced button.

  • Set Script DROP and CREATE to Script DROP.

  • Set Script Foreign Keys to True.

  • Click OK.

  • Click Next -> Next -> Finish.

  • View the script and then Execute.


If you drop the "child" table first, the foreign key will be dropped as well. If you attempt to drop the "parent" table first, you will get an "Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint." error.


Here is another way to drop all tables correctly, using sp_MSdropconstraints procedure. The shortest code I could think of:

exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
exec sp_MSforeachtable "drop table ?";