Not able to drop field in MariaDB/MySql table with FOREIGN KEY

Question

I tried to apply the workaround described in a similar question with no success.

In my scenario there are two related tables; I wish to drop the ean_code field from table product.

CREATE TABLE `brand` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(512) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `ean_code` varchar(255) DEFAULT NULL,
  `brand_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_eancode_unique` (`brand_id`,`ean_code`),
  CONSTRAINT `fk_product_brand_id` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

As explained by @bill-karwin the plain drop column fails:

> ALTER TABLE product drop column `ean_code`;
  Key column 'ean_code' doesn't exist in table

So I tried the suggested workaround "drop both the unique index and the field in the same statement":

> ALTER TABLE `product` DROP KEY `product_eancode_unique`, DROP COLUMN  `ean_code`;
  Cannot drop index 'product_eancode_unique': needed in a foreign key constraint

The error reported is quite peculiar in my opinion since the given field is NOT involved in any relation between the two tables;

I experimented the db<>fiddle with all available mariadb and mysql versions with no joy.

PS I've isolated the culprit constraint by trial and error using db-fiddle but ant db instance will do the job (apart from experimenting with different db versions).

lrkwz · Answer 1 · 2021-07-07T13:29:44.367

0

The workaround I've found myself is "drop the constraint before dropping index and field, then recreate the constraint".

alter table `product` drop constraint `fk_product_brand_id`;
alter table `product` drop key `product_eancode_unique`, drop column `ean_code`;
alter table `product` add foreign key `fk_product_brand_id`(`brand_id`) REFERENCES `brand`(`id`)

As in this db-fiddle.

Still I do not know how to quickly isolate the constraint causing all this.

edited Jul 07 '21 at 13:29

answered Jul 07 '21 at 13:19

lrkwz

6,105
3
36
59

Accepted @akina 's answer, my workaround leaves you without a useful index. – lrkwz Jul 07 '21 at 13:55

score 0 · Accepted Answer · answered Jul 07 '21 at 13:27

You cannot drop the column because the foreign key constraint uses this index. You must create an index which will be used for constraint checking firstly and drop index which includes the column to be dropped. Then the column may be dropped.

ALTER TABLE product ADD INDEX (brand_id), 
                    DROP INDEX product_eancode_unique,  
                    DROP COLUMN `ean_code`;

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=f36688300eeff3af867872b0227981ac

Not able to drop field in MariaDB/MySql table with FOREIGN KEY

2 Answers2

Linked