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 ?

migrated from stackoverflow.com Mar 22 '14 at 0:18

This question came from our site for professional and enthusiast programmers.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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