MySQL Forums
Forum List  »  InnoDB

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:)

Options: ReplyQuote


Subject
Views
Written By
Posted
creating innodb tabls - probs with foreign keys & references
3001
February 19, 2005 06:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.