MySQL 错误代码 1215:无法添加外键约束 [英] MySQL Error Code 1215: Cannot add foreign key Constraint

问题描述

我对 SQL 非常陌生，我正在尝试定义 2 个表 Hospital 和 Hospital_Address 但是当我尝试在 Hospital_Address 它抛出一个错误:"1215: 无法添加外键"

I'm very new to SQL, I'm trying to define a 2 tables Hospital and Hospital_Address but when I'm trying to add foreign key in Hospital_Address it throws an error: "1215: Cannot add foreign key"

create table Hospital (
             HId Int not null,  
           HName varchar(40) not null, 
           HDept int, Hbed Int, 
         HAreaCd int not null, 
         Primary Key (HId)
                      );

create table Hospital_Address (
                HAreaCd Int not null,
                  HArea varchar(40) not null,
                  HCity varchar(40), 
                  HAdd1 varchar(40),
                  HAdd2 varchar(40), 
                  Primary Key (HArea), 
                 foreign key (HAreaCd) references Hospital (HAreaCd));

请在这方面帮助我.提前致谢.

Please help me in this regard. Thanks in advance.

推荐答案

MySQL 要求父 Hospital 表中的 HAreaCd 列上有一个索引，以便供您在 FOREIGN KEY 约束中引用该列.

MySQL requires that there be an index on the HAreaCd column in the parent Hospital table, in order for you to reference that column in a FOREIGN KEY constraint.

规范模式是 FOREIGN KEY 引用父表的 PRIMARY KEY，尽管 MySQL 扩展了它以允许 FOREIGN KEY 引用作为 UNIQUE KEY 的列，而 InnoDB 扩展了它(超出了 SQL 标准)并允许 FOREIGN KEY 引用任何列集，只要有一个索引将这些列作为前导列(与外键约束中指定的顺序相同.)(也就是说，在 InnoDB 中，被引用的列不会不必是唯一的，尽管这种关系的行为可能不是您想要的.)

The normative pattern is for the FOREIGN KEY to reference the PRIMARY KEY of the parent table, although MySQL extends that to allow a FOREIGN KEY to reference a column that is a UNIQUE KEY, and InnoDB extends that (beyond the SQL standard) and allows a FOREIGN KEY to reference any set of columns, as long as there is an index with those columns as the leading columns (in the same order specified in the foreign key constraint.) (That is, in InnoDB, the referenced columns do not need to be unique, though the behavior with this type of relationship may not be what you intend.)

如果您在 Hospital 表中的该列上创建索引，例如:

If you create an index on that column in Hospital table, e.g.:

CREATE INDEX Hospital_IX1 ON Hospital (HAreaCd);

然后您可以创建一个引用该列的外键约束.

Then you can create a foreign key constraint that references that column.

然而，因为这是 MySQL 和 InnoDB 的非标准扩展，最佳实践"(如这里的其他答案所示)是 FOREIGN KEY 引用 PRIMARY KEY 外部表.理想情况下，这将是一列.

However, because this is a non-standard extension of MySQL and InnoDB, the "best practice" (as other answers here indicate) is for a FOREIGN KEY to reference the PRIMARY KEY of the foreign table. And ideally, this will be a single column.

鉴于 Hospital 表的现有定义，引用它的外键的更好选择是将 Hid 列添加到 Hospital_Address表

Given the existing definition of the Hospital table, a better option for a foreign key referencing it would be to add the Hid column to the Hospital_Address table

... ADD HId Int COMMENT 'FK ref Hospital.HId'

... ADD CONSTRAINT FK_Hospital_Address_Hospital 
       FOREIGN KEY (HId) REFERENCES Hospital (HId)

要建立行之间的关系，需要填充新的 HId 列的值.

To establish the relationship between the rows, the values of the new HId column will need to be populated.

这篇关于MySQL 错误代码 1215:无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！