MySQL error cannot add foreign key constraint
mysql
Solution
`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.
Problem
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> ```