How to drop table with all reference to this table

Question

I want to drop a table, and this table has be many table's foreign key.

So I want to drop all those reference (just refernece, not other table) within the drop table process.

Is there any simple SQL, such as:

Drop table table_name with all reference ?

Answer 1

You can use the following:

The following query will generate all the keys which you have to drop auto, then simply run:

SELECT CONCAT ('alter table ',
               TABLE_SCHEMA,
               '.',
               TABLE_NAME,
               ' DROP FOREIGN KEY ',
               CONSTRAINT_NAME,
               ';')
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
   AND TABLE_NAME = 'YourTableName';