mysql中不能添加外码约束,MySQL错误无法添加外键约束

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>

解决方案

price.p_code is not the primary key for price. Try:

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 can reference 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.