create table with foreign key

[Scott Purcell]

Hello,

I have this simple table of unique items.

CREATE TABLE ITEM (
       id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       manufacturer_id varchar(50),
) TYPE=InnoDB;

Foreach item, I can have 1 to many assets. So I am trying to use this:
CREATE TABLE ITEM_ASSET_REL (
       id INT,
       asset_id VARCHAR(10),
       FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE
) TYPE=InnoDB;

But I get the error 150. Now if I make the 'item_asset_rel' id UNIQUE, then it 
is happy. But that defeats my purpose of having many asset_id to one item.

Can anyone please, please help.

Thanks,


Scott K Purcell | Developer | VERTIS |
555 Washington Ave. 4th Floor | St. Louis, MO 63101 |
314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com

Vertis is the premier provider of targeted advertising, media, and 
marketing services that drive consumers to marketers more effectively. 
                                                 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]