0

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
Zanas_x3
  • 47
  • 6

0 Answers0