SQL DROP TABLE foreign key constraint

Question

If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?

GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
    DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
    DROP TABLE dbo.[Student]

Answer 1

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

Answer 2

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.

Answer 3

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_NAME(parent_object_id) + 
    ' DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

Marc

Answer 4

If I want to delete all the tables in my database

Then it's a lot easier to drop the entire database:

DROP DATABASE WorkerPensions

Answer 5

Check this link