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=20
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=3D1751

=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: foreign key problem in creating table

> Extension_ID int default 0,

try

Extension_ID int default null,

----- Original Message -----
From: "Alexander Nakhimovsky"
To:
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/win32?unsub=tony.yau@emigen.co.uk




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: foreign key problem in creating table

Alexander,

please use:

SHOW INNODB STATUS\G

to print a detailed explanation of error 150.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php


........................
> Extension_ID int default 0,

try

Extension_ID int default null,

----- Original Message -----
From: "Alexander Nakhimovsky"
To:
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/win32?unsub=1


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

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"
To:
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/win32?unsub=gcdmw-win32@m.gmane.org