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
4
  • 1
    Possible duplicate of MySQL Cannot drop index needed in a foreign key constraint Commented Feb 1, 2018 at 16:06
  • Thanks! But this looks like something different. Commented Feb 1, 2018 at 16:18
  • why you think is different? Commented Feb 1, 2018 at 16:19
  • Ok, yor are right :)) Error message stated that index is needed in a foreign key constraint. But actually it was just a key. Removing ordinary key helped. Commented Feb 1, 2018 at 16:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.