MySQL 错误:缺少约束索引 [英] MySQL error: Missing index for constraint
问题描述
我正在我的数据库中创建 2 个表:
DROP TABLE IF EXISTS `med_pharmacy`;如果不存在则创建表`med_pharmacy`(`med_pharmacy_id` int(11) NOT NULL AUTO_INCREMENT,`med_id` int(11) 非空,`med_barcode` varchar(45) 默认为空,`med_received` 日期默认为空,`med_expiry` 日期默认为空,`med_tablet` int(11) 默认为空,`med_pill` int(11) 默认为空,`clinic_id` varchar(45) 默认为空,主键(`med_pharmacy_id`),密钥`fk_med_pharmacy_medication1_idx`(`med_id`)) ENGINE=InnoDB AUTO_INCREMENT=1261 DEFAULT CHARSET=utf8mb4;
与:
DROP TABLE IF EXISTS `medication`;创建表`药物`(`med_id` int(11) 非空,`med_name` varchar(75) 非空,`med_date_ added` 日期默认为空,`clinic_id` varchar(45) 默认为空,`med_type` varchar(15) 默认 NULL) 引擎=InnoDB 默认字符集=utf8mb4;
当我在 wamp 中运行查询时,出现此错误:
SQL 查询:
ALTER TABLE `med_pharmacy`添加约束`fk_med_pharmacy_medication1`外键(`med_id`)参考`medication` (`med_id`) ON DELETE CASCADE ON UPDATE CASCADE MySQL
<块引用>
说:文档
#1822 - 添加外键约束失败.引用表中缺少约束fk_med_pharmacy_medication1"的索引'药物'
表格已经存在,但我更改了一个字段.
外键中引用的列必须编入索引.您需要在 medication.med_id
上添加索引.事实上,这应该是表的主键.
ALTER TABLE drug ADD PRIMARY KEY (med_id);
I am creating 2 tables in my database:
DROP TABLE IF EXISTS `med_pharmacy`;
CREATE TABLE IF NOT EXISTS `med_pharmacy` (
`med_pharmacy_id` int(11) NOT NULL AUTO_INCREMENT,
`med_id` int(11) NOT NULL,
`med_barcode` varchar(45) DEFAULT NULL,
`med_received` date DEFAULT NULL,
`med_expiry` date DEFAULT NULL,
`med_tablet` int(11) DEFAULT NULL,
`med_pill` int(11) DEFAULT NULL,
`clinic_id` varchar(45) DEFAULT NULL,
PRIMARY KEY (`med_pharmacy_id`),
KEY `fk_med_pharmacy_medication1_idx` (`med_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1261 DEFAULT CHARSET=utf8mb4;
AND:
DROP TABLE IF EXISTS `medication`;
CREATE TABLE `medication` (
`med_id` int(11) NOT NULL,
`med_name` varchar(75) NOT NULL,
`med_date_added` date DEFAULT NULL,
`clinic_id` varchar(45) DEFAULT NULL,
`med_type` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
And when I run the queries in wamp I got this error:
SQL query:
ALTER TABLE `med_pharmacy`
ADD CONSTRAINT `fk_med_pharmacy_medication1`
FOREIGN KEY (`med_id`)
REFERENCES
`medication` (`med_id`) ON DELETE CASCADE ON UPDATE CASCADE MySQL
said: Documentation
#1822 - Failed to add the foreign key constaint. Missing index for constraint 'fk_med_pharmacy_medication1' in the referenced table 'medication'
The tables already exists but I changed one field.
The column referenced in a foreign key must be indexed. You need to add an index on medication.med_id
. In fact, this should probably be the primary key of the table.
ALTER TABLE medication ADD PRIMARY KEY (med_id);
这篇关于MySQL 错误:缺少约束索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!