Mulugeta Maru wrote:
^^^^^^^^^^^^^Hi Michael,
I made some changes to the sql statments to create the ENROLLS table as follows:
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),
This is not an error, but it is redundant. You don't need a separate index on CourseID, because it is the left-most (first) column in your PRIMARY KEY. Unneeded indexes waste space and slow INSERTs. See the manual for details <http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html> and <http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html>.
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;
Change this to
CREATE TABLE ENROLLS
(
courseID SMALLINT NOT NULL,
sectionID SMALLINT NOT NULL,
studentID SMALLINT NOT NULL,
grade SMALLINT,
PRIMARY KEY(courseID,sectionID,studentID),
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;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), INDEX(courseID), FOREIGN KEY(courseID' at line 1
I do not know what wrong with my syntax. Please help. I am using MySQL version 4.1.3B.
Yes, your formatting choices make it hard to see errors. You should consider using indentation and line breaks to logically organize your queries for readability, and you might consider using something other than all caps for table names so as to distinguish them from SQL. There are two benefits to this: First, there are several experts on the list who won't read and respond to a message where the query is difficult to read. In other words, you improve your chances of getting useful help if you make it easy for us to read your queries. Second, you may find that formatting helps you catch problems yourself, without having to resort to the list. Just a suggestion.
Regards,
Maru
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
