creating innodb tabls - probs with foreign keys & references
Posted by:
Tom Austin
Date: February 19, 2005 06:42AM
CREATE TABLE `class` (
`classid` MEDIUMINT NOT NULL AUTO_INCREMENT,
`classname` VARCHAR( 30 ) NOT NULL ,
`username` VARCHAR( 30 ) NOT NULL ,
PRIMARY KEY ( `classid` )
FOREIGN KEY ( `username` )
REFERENCES user( `username` )
) TYPE = INNODB;
CREATE TABLE `user` (
`username` VARCHAR(30) NOT NULL,
`password` VARCHAR(30) NOT NULL,
`userlevel` INT NOT NULL,
PRIMARY KEY ( `username` )
) TYPE=INNODB;
CREATE TABLE `booking` (
`period` INT NOT NULL,
`date` DATE NOT NULL,
`roomname` VARCHAR(30) NOT NULL,
`username` VARCHAR(30) NOT NULL,
PRIMARY KEY ( `date` , `roomname` , `period` )
FOREIGN KEY ( `roomname` )
REFERENCES room( `roomname` )
FOREIGN KEY ( `username` )
REFERENCES user( `username` )
) TYPE=INNODB;
CREATE TABLE `room` (
`roomname` VARCHAR(30) NOT NULL,
PRIMARY KEY( `roomname` )
) TYPE=INNODB;
if i create them in the order...user, room, class, booking
and i get a 1064 error saying i have errors near my FOREIGN and REFERRENCES
the errors only occur on tables booking and class!! (beacuse there the ones with foreign key referneces)
can anyone see any problems
also, when i create a table (eg/ user) the table type in MyISAM, where i have specified InnoDB???
tom:)