Web Designing, Web Development, Website Design, Website Development, SEO

0091 98233 72069

COGS: CUSTOMISED, OPTIMISED, GENERALISED WEB DESIGN & WEB DEVELOPMENT SOLUTIONS

DROP all foreign keys in MYSQL database

Some times when importing data from other database if tables have foreign keys then you may face problem because of sequence of table insert queries. You must insert data into tables which are referred to other tables with foreign keys other wise you may face problem. There can be some other scenarios when foreign keys may create problem. Different ways are

1. Disable foreign key temporary – this is valid for single session on command prompt or scripting code using SET foreign_key_checks = 0;

2. Delete all foreign keys from all tables – you can do this by checking each table manually and delete keys or you can create alter table using following query. It will generate alter table query for each table which have foreign key. Copy all generated queries and execute them.

SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') FROM information_schema.key_column_usage WHERE CONSTRAINT_SCHEMA = 'db_name' AND referenced_table_name IS NOT NULL;