It seems that adding composite unique key with foreign keys included breaks the table: unable to drop unique key:
drop index test_unq on order_test
Error:
Cannot drop index 'test_unq': needed in a foreign key constraint
Steps to reproduce:
create table product_test (
id bigint unsigned not null auto_increment primary key
);
create table order_test (
id bigint unsigned not null auto_increment primary key,
product_id bigint unsigned not null,
foreign key (product_id) references product_test(id)
);
alter table order_test add some_field int not null;
alter table order_test add unique test_unq(product_id, some_field);
Running
SHOW CREATE TABLE order_test
returns
CREATE TABLE `order_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`product_id` bigint(20) unsigned NOT NULL,
`some_field` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `test_unq` (`product_id`,`some_field`),
CONSTRAINT `order_test_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES
`product_test` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Result looks invalid, there is a constraint but no key for product_id.
Any ideas how to recover table without dropping and creating it again?
Thanks!
EDIT:
Removing all other foreign keys does not help! Structure after removing:
CREATE TABLE `order_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`product_id` bigint(20) unsigned NOT NULL,
`some_field` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `test_unq` (`product_id`,`some_field`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Same error when I try to drop index.
drop index test_unq on order_test
Error:
Cannot drop index 'test_unq': needed in a foreign key constraint