Re-read the section on foreign keys
(http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html).
It clearly says (in the second and third bullets) that you must index the
columns on both ends of a FK.
Add an index to ITEM_ASSET_REL that contains id as its first term then you
will be able to setup the FK relationship. If you are doing it from
scratch, here is what the CREATE TABLE would look like:
CREATE TABLE ITEM_ASSET_REL (
id INT,
asset_id VARCHAR(10),
KEY(id),
FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE
) TYPE=InnoDB;
And, if you have another table of assets (called ASSET) and this table is
part of a many-to-many relationship (because each asset can be assigned to
more than one item at a time):
CREATE TABLE ITEM_ASSET_REL (
id INT,
asset_id VARCHAR(10),
KEY(id),
KEY(asset_id),
FOREIGN KEY(asset_id) references ASSET(id) on DELETE CASCADE
FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE
) TYPE=InnoDB;
Make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Scott Purcell" <[EMAIL PROTECTED]> wrote on 04/29/2005 12:32:56 PM:
> 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]
>
