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;
These two lines saved my day.
SET FOREIGN_KEY_CHECKS = 0;
flush privileges;
You rock