MySQL 4.0.15 : Foreign Key - ERROR 1005 (errno: 150)

[Andy Jefferson]

Hi,

I've got 2 tables in a circular dependency as follows

CREATE TABLE USERDETAILS
(
    USERDETAILS_ID BIGINT NOT NULL,
    FORENAME VARCHAR(30) BINARY NULL,
    SURNAME VARCHAR(30) BINARY NULL,
    USER_USER_ID_OID BIGINT NULL,
    PRIMARY KEY (USERDETAILS_ID)
) TYPE=INNODB

CREATE TABLE `USER`
(
    USER_ID BIGINT NOT NULL,
    DETAILS_USERDETAILS_ID_OID BIGINT NULL,
    LOGIN VARCHAR(20) BINARY NULL,
    PASSWORD VARCHAR(12) BINARY NULL,
    PRIMARY KEY (USER_ID)
) TYPE=INNODB

CREATE INDEX USERDETAILS_N49 ON USERDETAILS (USER_USER_ID_OID)

This all works fine.



I then try to create one of the foreign keys between the 2 tables
ALTER TABLE jpox.USERDETAILS ADD CONSTRAINT USERDETAILS_FK1 FOREIGN KEY
(USER_USER_ID_OID) REFERENCES jpox.`USER` (USER_ID)

MySQL (4.0.15) responds with 
ERROR 1005: Can't create table './jpox/#sql-5b3_5a.frm' (errno: 150)



This works with 4.0.18, yet I need my system to work also on 4.0.15 and
earlier. Is there something that I'm missing here with regards to my FK
specification ?


TIA
-- 
Andy


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