--- 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:
> >
> > 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
>
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
Any fruther 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.
Regards,
Maru
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
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]
