MySQL db problems

[fireorange]

I'm trying to create two foreign keys so that when I delete Gallery_ID 23 in the 'Galleries' table, it automatically deletes it in the 'Clicks' table but it isn't working.

CREATE TABLE `Galleries` (
`Gallery_ID` INT NOT NULL AUTO_INCREMENT ,
`Gallery_URL` VARCHAR( 255 ) NOT NULL ,
`Category` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `Gallery_ID` ) 
);

CREATE TABLE `Trades` (
`Trade_ID` INT NOT NULL AUTO_INCREMENT ,
`Trade_Name` VARCHAR( 255 ) NOT NULL ,
`Trade_URL` VARCHAR( 255 ) NOT NULL ,
`Trade_Domain` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `Trade_ID` )
);

CREATE TABLE `Clicks` (
`Gallery_ID` INT NOT NULL ,
`Trade_ID` INT NOT NULL ,
`Clicks` INT NOT NULL ,
`Used` INT NOT NULL ,
FOREIGN KEY ( `Gallery_ID` ) REFERENCES `Galleries` ( `Gallery_ID` ) ON UPDATE CASCADE ON DELETE CASCADE ,
FOREIGN KEY ( `Trade_ID` ) REFERENCES `Trades` ( `Trade_ID` ) ON UPDATE CASCADE ON DELETE CASCADE 
);

[willow]

If your default table type isn't innodb, then add engine=innodb to the create statements.

[fireorange]

Originally Posted by willow

If your default table type isn't innodb, then add engine=innodb to the create statements.

I tried adding TYPE=InnoDB at the end but it gets created as INNODB everytime.

Server version is: MySQL 3.23.58

Is it enabled by default in that version? Any command to check?

[willow]

http://dev.mysql.com/doc/mysql/en/innodb-overview.html

You probably need to enable it. Have a read. If you can, move to 4.1.x, much nicer.

[fireorange]

Thanks I'll get my host to upgrade my server

[willow]

Make sure you dump your tables out before the upgrade, or some other kind of backup. Good luck.

[chompers]

bump for posterity's sake

WOOOOOOOOOOOO WHOOOOOOOOOOOOOOOOOO!!!!!!!!!