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>

This is just a guess, but shouldn't price.p_code and movie.p_code be indexes in their own tables? — gen_Eric
– gen_Eric, Commented Apr 5, 2013 at 19:38

Community · Accepted Answer · 2017-05-23 12:16:50Z

7

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.

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Apr 5, 2013 at 19:39

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bartosz Firyn · Accepted Answer · 2020-06-04 13:25:05Z

7

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.

edited Jun 4, 2020 at 13:25

Bartosz Firyn

2,6742 gold badges22 silver badges18 bronze badges

answered Jan 25, 2016 at 11:29

am0wa

8,4753 gold badges44 silver badges37 bronze badges

1 Comment

Tim Over a year ago

Thanks for this helpful details. Had the same issues. I'd created one table with DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin and the referenced table was created with DEFAULT CHARSET=utf8mb4 only. Using the same collation and everything works as expected. :)

Justin McDonald · Accepted Answer · 2013-04-05 19:40:05Z

2

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

answered Apr 5, 2013 at 19:40

Justin McDonald

2,16616 silver badges19 bronze badges

Comments

internally1 · Accepted Answer · 2013-04-05 19:49:44Z

0

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

answered Apr 5, 2013 at 19:49

internally1

976 silver badges18 bronze badges

Comments

amk · Accepted Answer · 2013-04-05 20:10:06Z

0

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

answered Apr 5, 2013 at 20:10

amk

2821 silver badge3 bronze badges

Comments

xbral · Accepted Answer · 2016-08-31 13:09:06Z

0

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.

answered Aug 31, 2016 at 13:09

xbral

911 silver badge4 bronze badges

Collectives™ on Stack Overflow

MySQL error cannot add foreign key constraint

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related