Removing Foreign Key Constraints In MYSQL


Some Mysql db is using innoDB which implements foreign key contraints. If you can’t drop or alter a table, check that it doesnt have foreign key contraints. Well, innoDB can be an angel or devil…

In mysql command line, check existence of foreign keys

show create table demographic_type

CREATE TABLE `demographic_type` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_group_id` varchar(255) default NULL,
`venue_channel_id` int(10) unsigned default NULL,
`product_list_id` varchar(255) default NULL,
`promotion_type_id` varchar(20) default NULL,
`required_drivers` varchar(32) default NULL,
`sequence` tinyint(3) unsigned default NULL,
`name` varchar(128) default NULL,
`description` varchar(255) default NULL,
`image` varchar(255) default NULL,
`author` int(10) unsigned default NULL,
`submitted` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`ip` varchar(16) default '127.0.0.1',
`publish` enum('active','inactive','pending') default 'pending',
PRIMARY KEY (`id`),
KEY `venue_channel_id` (`venue_channel_id`),
KEY `product_list_id` (`product_list_id`),
CONSTRAINT `0_1013` FOREIGN KEY (`venue_channel_id`) REFERENCES `venue_channel` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 PACK_KEYS=1 COMMENT='List of demographic categories'

ok this table has a contraint. Let’s drop the contraint

alter table demographic_type DROP FOREIGN KEY 0_1013;

Sometimes its too hard to remove foreign key constraints. If the foreign is a key as well, remove the key first.

alter table table_name drop key xxx;
alter table table_name drop foreign key xxx;

or remove foreign key constraint check, then modify or drop the table.

SET FOREIGN_KEY_CHECKS = 0;
flush privileges;

Like it.? Share it:

2 Responses to Removing Foreign Key Constraints In MYSQL

  1. Pingback: Quick Mysql Tips | LAMP How To - Open Source At Work

  2. Ali
    February 8, 2013 at 8:59 pm

    These two lines saved my day.
    SET FOREIGN_KEY_CHECKS = 0;
    flush privileges;

    You rock