mysqlsqlforeign-keyscomposite-primary-keymysql-error-1005

Foreign key in MySql as composite primary key


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?


Solution

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