[Middlegen-user] Pb Middlegen with ALTER TABLE

[r***@fr.thalesgroup.com]

Hello,


I have a problem when I used middlegen with foreign key generated with
'ALTER TABLE' SQL command.

here is a script that creates two tables with one relation:

CREATE TABLE obj_item (
OBJ_ITEM_ID BIGINT (15) NOT NULL,
PRIMARY KEY ( OBJ_ITEM_ID ) )TYPE=InnoDB;

CREATE TABLE org (
ORG_ID BIGINT (15) NOT NULL,
PRIMARY KEY ( ORG_ID ) )TYPE=InnoDB;

ALTER TABLE org
ADD INDEX my_ind(ORG_ID),
ADD FOREIGN KEY (ORG_ID) REFERENCES GH4.OBJ_ITEM (OBJ_ITEM_ID)
ON DELETE CASCADE;

Note: the INDEX/FOREIGN KEY is outside the 'CREATE TABLE'

No error are logged by mysql monitor. So the foreign key seems to be
accepted.


If I try middlegen on the database (MySQL-Max 4.0.1), the two tables are
well detected but without relation.

The same script but with INDEX/FOREIGN KEY in the 'CREATE TABLE' command is
OK:

CREATE TABLE obj_item (
OBJ_ITEM_ID BIGINT (15) NOT NULL,
PRIMARY KEY ( OBJ_ITEM_ID ) )TYPE=InnoDB;

CREATE TABLE org (
ORG_ID BIGINT (15) NOT NULL,
INDEX my_ind(ORG_ID),
FOREIGN KEY (ORG_ID) REFERENCES GH4.OBJ_ITEM (OBJ_ITEM_ID),
PRIMARY KEY ( ORG_ID ) )TYPE=InnoDB;

Anyone has an idea of the problem ? Is it a middlegen or a mySQL problem ?

Thanks to help me.

Richard.

[Steve Knight]

I don't believe you can use ALTER statement to add foreign keys in
MySQL...it must be done in the CREATE TABLE statement. Check the MySQL docs
for more info.

Steve


----- Original Message -----
From: <***@fr.thalesgroup.com>
To: <middlegen-***@lists.sourceforge.net>
Sent: Monday, April 08, 2002 9:00 AM
Subject: [Middlegen-user] Pb Middlegen with ALTER TABLE

Post by r***@fr.thalesgroup.com
Hello,
I have a problem when I used middlegen with foreign key generated with
'ALTER TABLE' SQL command.
CREATE TABLE obj_item (
OBJ_ITEM_ID BIGINT (15) NOT NULL,
PRIMARY KEY ( OBJ_ITEM_ID ) )TYPE=InnoDB;
CREATE TABLE org (
ORG_ID BIGINT (15) NOT NULL,
PRIMARY KEY ( ORG_ID ) )TYPE=InnoDB;
ALTER TABLE org
ADD INDEX my_ind(ORG_ID),
ADD FOREIGN KEY (ORG_ID) REFERENCES GH4.OBJ_ITEM (OBJ_ITEM_ID)
ON DELETE CASCADE;
Note: the INDEX/FOREIGN KEY is outside the 'CREATE TABLE'
No error are logged by mysql monitor. So the foreign key seems to be
accepted.
If I try middlegen on the database (MySQL-Max 4.0.1), the two tables are
well detected but without relation.
The same script but with INDEX/FOREIGN KEY in the 'CREATE TABLE' command is
CREATE TABLE obj_item (
OBJ_ITEM_ID BIGINT (15) NOT NULL,
PRIMARY KEY ( OBJ_ITEM_ID ) )TYPE=InnoDB;
CREATE TABLE org (
ORG_ID BIGINT (15) NOT NULL,
INDEX my_ind(ORG_ID),
FOREIGN KEY (ORG_ID) REFERENCES GH4.OBJ_ITEM (OBJ_ITEM_ID),
PRIMARY KEY ( ORG_ID ) )TYPE=InnoDB;
Anyone has an idea of the problem ? Is it a middlegen or a mySQL problem ?
Thanks to help me.
Richard.
_______________________________________________
middlegen-user mailing list
https://lists.sourceforge.net/lists/listinfo/middlegen-user