Why does creating the following table give me the error: ERROR 1005
(HY000): Can't create table (errno: 150)

CREATE TABLE orderProducts (
orderID integer(10),
productID integer(10),
quantity integer(10),
PRIMARY KEY (orderID, productID),
FOREIGN KEY (orderID) REFERENCES orders (orderID),
FOREIGN KEY (productID) REFERENCES products (productID)
);

Can i not say that attributes that are part of the primary key are also
foreign keys?

- Jason

On 23 Jun 2006 00:58:49 -0700, jason.m.ho [at] gmail.com wrote:

>Why does creating the following table give me the error: ERROR 1005
>(HY000): Can't create table (errno: 150)
>
>CREATE TABLE orderProducts (
>orderID integer(10),
>productID integer(10),
>quantity integer(10),
>PRIMARY KEY (orderID, productID),
>FOREIGN KEY (orderID) REFERENCES orders (orderID),
>FOREIGN KEY (productID) REFERENCES products (productID)
>);
>
>Can i not say that attributes that are part of the primary key are also
>foreign keys?

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-co nstraints.html

You'll need an index on productID.
It ought to be able to use the primary key index for orderID, though.

--
Andy Hassall :: andy [at] andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool