Mulugeta Maru wrote:
--- Michael Stassen <[EMAIL PROTECTED]> wrote:
Mulugeta Maru wrote:
Hi Michael,
I made some changes to the sql statments to create the ENROLLS table as follows:
****** Did you read this part? ******
^^^^^^^^^^^^^CREATE TABLE ENROLLS ( courseID SMALLINT NOT NULL, sectionID SMALLINT NOT NULL, studentID SMALLINT NOT NULL, grade SMALLINT)TYPE=INNODB;
You made one too many changes. Your error is now
here. You've ended the CREATE TABLE statement when you weren't really done.
****** *********************** ******
PRIMARY KEY(courseID,sectionID,studentID), INDEX(courseID),
<snip>
FOREIGN KEY(courseID) REFERENCES COURSES(courseID) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX(sectionID),
FOREIGN KEY(sectionID) REFERENCES SECTIONS(sectionID)
ON UPDATE CASCADE ON DELETE CASCADE,
INDEX(studentID),
FOREIGN KEY(studentID) REFERENCES STUDENTS(studentID)
ON UPDATE CASCADE ON DELETE CASCADE
)TYPE=INNODB;
<snip>
I removed the index and still get the following error message:
The error message is as follows: ERROR 1064 at line 38: 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 'PRIMARY KEY (countID, sectionID, studentID), FOREIGN KEY(courseID) REFERENCES COUR' at line 1
The same error as before. As I said, the index was not the error. It appears that you still didn't remove the extraneous ")TYPE=INNODB;" in the middle of your statement. "PRIMARY KEY..." makes sense as part of a CREATE TABLE statement, it does not make sense as the beginning of a separate query. Did you try copying and pasting the query I gave you?
Any further thought on this error. As you can see from the sql statement the three fields courseID,sectionID, studentID are primery keys in tables COURSES, SECTIONS, and STUDENTS respectively. What I am trying to do is to make the three fields together to be primary key in the ENROLL table and to make each of them foreign keys. At the same time I would like also to have cascade update and delete.
No problem with that. You just have to get the syntax right.
Regards,
Maru
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]