Method to drop the master table in SQL

Asked By 10 points N/A Posted on -
qa-featured

 

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 ?

SHARE
Answered By 0 points N/A #98415

Method to drop the master table in SQL

qa-featured

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.

Login/Register to Answer

Related Questions