Foreign key in MySql as composite primary key

Question

I have the database with the name Shop with this 3 tables:

create table usr(
    id_usr varchar(20) not null,
    primary key(id_usr)

);

create table product(
    id_product varchar(20) not null,
    id_size varchar(20) not null,
    price float(4,2) unsigned,
    primary key(id_product,id_size)
);

create table cart(
    myUser varchar(20), 
    mySize varchar(20),
    product varchar(20),
    qty int not null,
    primary key(myUser,product,mySize),
    FOREIGN KEY (myUser) REFERENCES usr (id_usr),
    FOREIGN KEY (product) REFERENCES product (id_product),
    FOREIGN KEY (mySize) REFERENCES product (id_size)
);

when I compile in sql, it gives to me this message:

1005 - Can't create table 'Shop.cart' (errno: 150)

If I try to delete the foreign key mySize (FOREIGN KEY (mySize) REFERENCES prodotto (id_size)) it works, why have I this message?

Where is your prodotto table? – sgeddes May 20 '14 at 21:05 — sgeddes, May 20 '14 at 21:05

TommCatt · Accepted Answer · 2017-05-01T03:16:59.620

6

You're making a FK reference to product table but defining only part of the key. Try...

FOREIGN KEY (product, mySize) REFERENCES product (id_product, id_size),

edited May 01 '17 at 03:16

answered May 21 '14 at 07:10

TommCatt

5,498
1
13
20

score 2 · Answer 2 · answered May 20 '14 at 21:08

My guess is you haven't created your prodotto table yet. This works:

create table user(
    id_user varchar(20) not null,
    primary key(id_user)

);

create table product(
    id_product varchar(20) not null,
    id_size varchar(20) not null,
    price float(4,2) unsigned,
    primary key(id_product,id_size)
);

create table prodotto (
  id_size varchar(20) primary key
);

create table cart(
    myUser varchar(20), 
    mySize varchar(20),
    product varchar(20),
    qty int not null,
    primary key(myUser,product,mySize),
    FOREIGN KEY (myUser) REFERENCES user (id_user),
    FOREIGN KEY (product) REFERENCES product (id_product),
    FOREIGN KEY (mySize) REFERENCES prodotto (id_size)
);

SQL Fiddle Demo

I wronged to write, because I did a translation of my db to make it more easy to understand and I forgot to translate prodotto — joumvaer92, May 21 '14 at 06:52

Foreign key in MySql as composite primary key

1005 - Can't create table 'Shop.cart' (errno: 150)

2 Answers2