MySQL error cannot add foreign key constraint

Question

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>

Answer 1

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.

Answer 2

• Engine should be the same e.g. InnoDB
• Datatype should be the same, and with same length. e.g. VARCHAR(20)
• Collation Columns 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 primary key) in the referenced table.