MySQL MySQL错误无法添加外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15842111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL error cannot add foreign key constraint
提问by Daniel
what is wrong?
怎么了？
mysql> create table price(
-> p_code char(1) not null,
-> p_description varchar(20),
-> p_rentfee decimal(2,2) not null,
-> p_dylatefee decimal(2,2));
Query OK, 0 rows affected (0.18 sec)
mysql> create table movie(
-> mv_no char(4) not null,
-> mv_name varchar(50) not null,
-> mv_year char(4) not null,
-> mv_cost decimal(2,2) not null,
-> mv_genre varchar(15) not null,
-> p_code char(1) not null,
-> foreign key (p_code) references price(p_code));
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql>
回答by lc.
price.p_codeis not the primary key for price. Try:
price.p_code不是 的主键price。尝试：
create table price(
p_code char(1) not null PRIMARY KEY,
p_description varchar(20),
p_rentfee decimal(2,2) not null,
p_dylatefee decimal(2,2));
In general, foreign keys must reference a primary/unique key, a whole primary/unique key, and nothing but a primary/unique key.
一般来说，外键必须引用一个主/唯一键、一个完整的主/唯一键，而只有一个主/唯一键。
In some RDBMS, for example SQL Server, you canreference a column with a unique index(not key) (see can we have a foreign key which is not a primary key in any other table?), but this is non-standard behavior.
在一些RDBMS，例如SQL Server，您可以引用列，具有独特的索引（未键）（见我们能有一个外键这是不以任何其他表的主键？），但是这是不规范的行为.
回答by am0wa
- Engineshould be the same e.g. InnoDB
- Datatypeshould be the same, and with same length. e.g. VARCHAR(20)
- CollationColumns charset should be the same. e.g. utf8
Watchout: Even if your tables have same Collation, columns still could have different one. - Unique- Foreign key should refer to field that is unique (usually private key) in the reference table.
- 引擎应该是相同的，例如 InnoDB
- 数据类型应该相同，并且长度相同。例如 VARCHAR(20)
- CollationColumns 字符集应该相同。例如 utf8
Watchout：即使您的表具有相同的排序规则，列仍然可以具有不同的排序规则。 - 唯一- 外键应指代参考表中唯一的字段（通常是私钥）。
回答by Justin McDonald
p_codeshould be a primary key in your pricetable:
p_code应该是price表中的主键：
create table price(
-> p_code char(1) not null,
-> p_description varchar(20),
-> p_rentfee decimal(2,2) not null,
-> p_dylatefee decimal(2,2),
-> PRIMARY KEY ( p_code ));
回答by internally1
set p_codeto be a key ,either set it to be a unique keyor primary key.
将p_code设置为键，或者将其设置为唯一键或主键。
回答by amk
- The referenced column
price.p_codemust be unique (primary or unique key need to be created). - Both tables must be InnoDbtables, use
ENGINE = INNODBinCREATE TABLEstatement.
- 引用的列
price.p_code必须是唯一的（需要创建主键或唯一键）。 - 两个表都必须是InnoDb表，
ENGINE = INNODB在CREATE TABLE语句中使用。
回答by kbral
The data type for the child column must match the parent column exactly. For example, since price.p_code is an char(1), movie.p_code also needs to be an char(1) and price.p_code need be a Primary Key or need create a Index.
子列的数据类型必须与父列完全匹配。例如，由于 price.p_code 是一个 char(1)，movie.p_code 也需要是一个 char(1)，而 price.p_code 需要是一个主键或需要创建一个索引。
