Hi Michael, I must say thank you for helping me. Yes you are right - I made the changes and it works.
I will have to read the manual to further understand the issue with Primary and Foreign keys. Thank you. Maru --- Michael Stassen <[EMAIL PROTECTED]> wrote: > The problem is in table SECTIONS. From the manual, > "In the referencing > table, there must be an index where the foreign key > columns are listed as > the first columns in the same order. In the > referenced table, there must be > an index where the referenced columns are listed as > the first columns in the > same order." > <http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html> > > 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 > > __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
