1

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 ?

0

1 Answer 1

3

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'; 
2
  • This gives me the foreign keys of 'YourTableName' rather than the foreign keys referring to the 'YourTableName'. Am I wrong?
    – mmdemirbas
    Aug 13, 2018 at 11:25
  • This will drop the table's references itself to other tables, not the "referring to" keys
    – Adel
    Sep 9, 2018 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.