Method to drop the master table in SQL

Asked By jgulliver 10 points N/A Posted on - 04/01/2012

Which method should be used to drop the master table if its primary key is being referenced by a foreign key in some other table ?

Answered By Estillero State 5 points N/A #98415

Hi Jgulliver,

Before can you drop the master table you have to drop the foreign key on the other table first. To drop foreign key, use the following SQL:

MySQL:

ALTER TABLE <table_name>
DROP FOREIGN KEY <f_key_name>

SQL Server / Oracle / MS Access:

ALTER TABLE <table_name>
DROP CONSTRAINT fk_PerOrders

In addition, you need to make sure that there are no other foreign key relationships referencing your table.

SELECT *FROM sys.foreign_keys

WHERE referenced_object_id = object_id('<primary_table_name>')

And if there are any, drop those foreign key relations:

SELECT

'ALTER TABLE ' + OBJECT_NAME(parent_object_id) +

' DROP CONSTRAINT ' + name

FROM sys.foreign_keys

WHERE referenced_object_id = object_id('<primary_table_name>')

Now you are free to drop the primary table

DROP TABLE <table_name>

I hope it helped.

Questions
7

Answers
70

Best Answers
7

Its Question For Embedded Programmers