无法使用 FOREIGN KEY 删除 MariaDB/MySql 表中的字段 [英] Not able to drop field in MariaDB/MySql table with FOREIGN KEY

问题描述

我尝试应用 类似问题 没有成功.

在我的场景中有两个相关的表；我希望从表 product 中删除 ean_code 字段.

<块引用>

CREATE TABLE `brand` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`name` varchar(512) NOT NULL,主键(`id`)) 引擎=InnoDB 默认字符集=utf8

<块引用>

创建表`product` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,`ean_code` varchar(255) 默认为空，`brand_id` bigint(20) 默认为空，主键(`id`)，唯一键`product_eancode_unique`(`brand_id`，`ean_code`)，约束 `fk_product_brand_id` 外键 (`brand_id`) 参考 `brand` (`id`)) 引擎=InnoDB 默认字符集=utf8；

正如@bill-karwin 所解释的，普通的 drop column 失败:

>ALTER TABLE 产品删除列 `ean_code`;表中不存在键列ean_code"

所以我尝试了建议的解决方法在同一语句中同时删除唯一索引和字段":

>ALTER TABLE `product` DROP KEY `product_eancode_unique`, DROP COLUMN `ean_code`;无法删除索引product_eancode_unique":外键约束中需要

在我看来，报告的错误很奇怪，因为给定的字段不涉及两个表之间的任何关系；

我用所有可用的 mariadb 试验了 db<>fiddle和没有欢乐的mysql版本.

PS 我已经使用 db-fiddle 通过反复试验来隔离罪魁祸首约束，但 ant db 实例会完成这项工作(除了尝试不同的 db 版本).

解决方案

您不能删除列，因为外键约束使用此索引.您必须首先创建一个用于约束检查的索引，然后删除包含要删除的列的索引.然后该列可能会被删除.

ALTER TABLE product ADD INDEX (brand_id),DROP INDEX product_eancode_unique,删除列`ean_code`；

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

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).

解决方案

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

这篇关于无法使用 FOREIGN KEY 删除 MariaDB/MySql 表中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！