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 '',
`publish` enum('active','inactive','pending') default 'pending',
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.

flush privileges;

Author: bpeh

Bernard Peh is a great passioner of web technologies and one of the co-founder of Website Design and Reviews. He works with experienced web designers and developers everyday, developing and designing commercial websites. He specialises mainly in SEO and PHP programming.

2 thoughts on “Removing Foreign Key Constraints In MYSQL”

Comments are closed.