Foreign key in INNODB

[vinita Vigine Murugiah]

HI
I'm not sure what is wrong with my CREATE TABLE syntax, Please advice



mysql> CREATE TABLE equipment (
-> equipID CHAR(20) NOT NULL,
-> assetNumber CHAR(20),
-> serialNumber CHAR(50),
-> description BLOB,
-> barcode CHAR(20),
-> room CHAR(20),
-> area CHAR(20),
-> owner CHAR(100),
-> createDate DATE,
-> PRIMARY KEY (equipID)
-> ) TYPE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE equipment_interfaces (
-> equipID CHAR(20) NOT NULL,
-> ifName CHAR(20) NOT NULL,
-> ifType ENUM("10Mbs","100Mbs","1Gb","CDDI","FDDI"),
-> ifNet CHAR(50),
-> ifIPv4addr CHAR(20),
-> INDEX (equipID),
-> FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE,
-> PRIMARY KEY (equipID, ifName)
-> ) TYPE=INNODB;
Query OK, 0 rows affected (0.01 sec)

***********************************************************************************************************************************************
mysql> CREATE TABLE equipment_interfaces_IPv4alias (
-> equipID CHAR(20) NOT NULL,
-> ifName CHAR(20) NOT NULL,
-> ifIPv4alias CHAR(20) NOT NULL,
-> INDEX (equipID),
-> FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE,
-> INDEX (ifName),
-> FOREIGN KEY (ifName) REFERENCES equipment_interfaces (ifName) ON DELETE CASCADE,
-> PRIMARY KEY (equipID, ifName, ifIPv4alias)
-> ) TYPE=INNODB;
ERROR 1005: Can't create table './test/equipment_interfaces_IPv4alias.frm' (errno: 150)
mysql>
*************************************************************************************************************************************************

[vinita@mungah] vinita [1:54] perror 150
Error code 150: Unknown error: 150

--


warm regards
Vinita Vigine Murugiah
Email : [EMAIL PROTECTED] Ph : (03) 8344 1273



---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php