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.

Answer 3

p_code should be a primary key in your price table:

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 ));

Answer 4

set p_code to be a key ,either set it to be a unique key or primary key.

Answer 5

1. The referenced column price.p_code must be unique (primary or unique key need to be created).
2. Both tables must be InnoDb tables, use ENGINE = INNODB in CREATE TABLE statement.

Answer 6

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.