re: foreign key problem in creating table

Anybody know if the trouble getting the MySql Administrator to allow setup
of foreign key was resolved? I saw a post from last month about error 150,
but setting my default value to null as suggested, did not fix it.

A 150 error with the following message was returned when I tried to set up a
foreign key using the MySQL administrator (windows 2000 environment)...

Can't create table '.\databasename\#sql-72c_2f.frm' (errno: 150)

the sql generated (as reported by the administrator) was ....

 ADD CONSTRAINT 'FK_myname' FOREIGN KEY 'FK_myname' ('title') REFERENCES
'contactaddressestitle' ('id') ;

----- Original Message ----- 
From: "Alexander Nakhimovsky" <adnakhimovsky@stripped>
To: <win32@stripped>
Sent: Sunday, January 08, 2006 5:25 PM
Subject: foreign key problem in creating table


I am running Ver 14.12 Distrib 5.0.17, for Win32 (ia32) on WinXP.

The code below gives me errno: 150 error (but executes on Linux):

create table METADATA_ELEM (
    Element_ID      int auto_increment not null,
    TagName         varchar(255) not null,
    Lang            varchar(255),
    Content         text,
    Extension_ID    int default 0,
    Type            varchar(20),
    Code            varchar(255) default '',
    Item_ID         int,
    Tag_ID          int,
primary key (Element_ID),
foreign key (Extension_ID) references EXTENSION (Extension_ID)
        on delete set null
        on update cascade,
foreign key (Item_ID) references ARCHIVED_ITEM (Item_ID)
        on delete set null
        on update cascade,
foreign key (Tag_ID) references ELEMENT_DEFN (Tag_ID)
        on delete set null
        on update cascade);

However, this code runs without error:

create table ARCHIVED_ITEM (
Item_ID int auto_increment not null,
OaiIdentifier varchar(255) not null,
DateStamp date not null,
Archive_ID int,
Schema_ID int,
primary key (Item_ID),
foreign key (Archive_ID) references OLAC_ARCHIVE (Archive_ID)
        on delete set null
        on update cascade,
foreign key (Schema_ID) references SCHEMA_VERSION (Schema_ID)
        on delete set null
        on update cascade);

Thank you for your help

Alexander Nakhimovsky
Computer Science Department
Colgate University Hamilton NY 13346
  http://cs.colgate.edu/~sasha
Director, Project Afghanistan
  http://www.colgate.edu/desktopdefault1.aspx?tabid=1751



-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]