SQL DROP TABLE foreign key constraint

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 ?";

If it is SQL Server you must drop the constraint before you can drop the table.

SQL DROP TABLE foreign key constraint

Related

Recent Posts