In SECTIONS, you have an unnecessary index on courseID (it's the first column in the primary key) and no index where sectionID comes first (it's second in the primary key). The former is simply inefficient, the latter is causing the error. Most likely, you should drop the index on courseID and add one on sectionID:
ALTER TABLE SECTIONS DROP INDEX courseID, ADD INDEX (sectionID);
After you do that, the ENROLLS table creation should work without error.
Keeping the courseID index and reversing the primary key should also work, but a primary key on (sectionID, courseID) makes less logical sense, I think.
Michael
Mulugeta Maru wrote:
I have posted this in a subject called - InnoDB table
creation. I am just trying to be specific. Please
forgive me if this is not allowed.
I have searched the online help and this site. I can not find out why I am getting this error:
ERROR 1005 at line 33: Can't creat table '.\enrollment1\enrolls.frm' (errno: 150)
Here is what I am trying to do:
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;
Pleas help if you can. Thank you. Maru
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
