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?

Bobbee

Where is your prodotto table? – Drying

Answer 1

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

Mal

Answer 2

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

Drying

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 – Bobbee