Blurgh!!! My first mySQL Database won't CREATE!!!

lo all!

**Please note, I don't want you to do my college course for me**

I'm trying to implement the following database on a mySQL server:

CREATE TABLE room (roomid CHAR(6) NOT NULL, singlesex CHAR(1), smoking CHAR(1), roomtype Char(1), disabled CHAR(1), catered CHAR(1),
PRIMARY KEY (roomid),
FOREIGN KEY (roomid) REFERENCES roomass (roomid)
ON DELETE CASCADE ON UPDATE CASCADE ) ;

CREATE TABLE roomass (studentid INT4, roomid CHAR(6) NOT NULL, 
PRIMARY KEY (roomid), 
FOREIGN KEY (roomid) REFERENCES room (roomid) 
ON DELETE CASCADE ON UPDATE CASCADE, 
FOREIGN KEY (studentID) REFERENCES student (studentid) 
ON DELETE RESTRICT ON UPDATE RESTRICT,) ;

CREATE TABLE student (studentid INT4 NOT NULL, password VARCHAR(10), name VARCHAR(30), age INT, dateofbirth CHAR(6), gender CHAR(1), permanentaddress VARCHAR(50), emailaddress VARCHAR(30), phonenumber INT, course CHAR(3), definitelyaccepted CHAR(1), definitelyrejected CHAR(2)
PRIMARY KEY (studentid), 
FOREIGN KEY (studentid) REFERENCES roomass (studentid) 
ON DELETE RESTRICT ON UPDATE RESTRICT 
FOREIGN KEY (studentid) REFERENCES studentapp (studentid) 
ON DELETE CASCADE ON UPDATE CASCADE ) ;

CREATE TABLE studentapp (studentid INT4 NIT NULL, disabled CHAR(1), smoker CHAR(1), pref1 CHAR(6), pref2 CHAR(6), pref3 CHAR(6), roomtype CHAR(1), singlesex CHAR(1), smoking CHAR(1), overseas CHAR(1), outsidem25 CHAR(1), finalyear CHAR(1), previousresident CHAR(1)
PRIMARY KEY (studentid), 
FOREIGN KEY (studentid) REFERENCES roomass (studentid) 
ON DELETE RESTRICT ON UPDATE RESTRICT 
FOREIGN KEY (studentid) REFERENCES student (studentid) 
ON DELETE CASCADE ON UPDATE CASCADE ) ;

I have attached a relational diagram so u can see what I mean it to look like.


room and roomass have created, but student and studentapp give me the following message:

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '(studentid),
FOREIGN KEY (studentid) REFERENCES roomass (stude

Seth Brundle

Are you trying to reference a field in a non-existent table with the FK in the room table?

Well, the Foreign Key for room references roomass, these CREATE fine, I only get the error message with student and studentapp though!!


Please guys, Im tight on a deadline here, and am real stuck, all the documentation Im basing my work on sez this should be working fine!!!!!

Seth Brundle

Check if your absent commas in both are not affecting their creation
Also there is an error in
CREATE TABLE studentapp (studentid INT4 NIT NULL...

YEAH!!!!!!!!!!!!

It's the second comma for the second foreign key!!!!!!!!!!!!!!


It all begins to make sense now, I only but one in as a dividor with the PRIMARY KEY

Brilliant!!!!!!


Thanks alot man, that was holdin me so far back you wouldnt believe

Seth Brundle

you are missing a comma from each of students & studentsapp