InnoDB Table Creation

[asfaw]

Does any one know why the sql command below does not work?

CREATE TABLE ENROLLS
(enrollmentID INT NOT NULL AUTO_INCREMENT,
courseID SMALLINT NOT NULL,
sectionID SMALLINT NOT NULL,
studentID SMALLINT NOT NULL,
grade SMALLINT,
PRIMARY KEY(enrollmentID),
INDEX(courseID),
REFERENCES COURSES(courseID)ON UPDATE CASCADE ON DELETE CASCADE,
INDEX(sectionID),
REFERENCES SECTIONS(sectionID)ON UPDATE CASCADE ON DELETE CASCADE,
INDEX(studentID),
REFERENCES STUDENTS(studentID)ON UPDATE CASCADE ON DELETE CASCADE)TYPE=INNODB;

Your help is very much appreciated.

Maru

 

[Bastien]

Read:

http://dev.mysql.com/doc/mysql/en/InnoDB_init.html

and

http://dev.mysql.com/doc/mysql/en/Using_InnoDB_tables.html

have you created the innodb tablespace? and database?


TYPE=INNODB

s/b

ENGINE=InnoDB

Bastien

Cat, the other other white meat

 

[asfaw]

Thank you Bastien.

Yes I have created the innodb tablespace and database. To make it easy for you to see I am going to include the complete table creation sql statement. Except the Enrolls table the other tables are created with no problem. This lead me to believe that it is the sql syntax I am using that is not correct.

CREATE TABLE STUDENTS
(studentID SMALLINT NOT NULL,
student_name char(18),
address char(20),
city char(10),
state char(2),
zip char(5),
gender char(1),
PRIMARY KEY (studentID))TYPE=INNODB;
CREATE TABLE TEACHERS
(teacherID SMALLINT NOT NULL,
teacher_name char(18),
phone char(10),
salary FLOAT, PRIMARY KEY(teacherID))TYPE=INNODB;
CREATE TABLE COURSES
(courseID SMALLINT NOT NULL,
course_name char(20),
department char(16),
num_credits SMALLINT,
PRIMARY KEY(courseID))TYPE=INNODB;
CREATE TABLE SECTIONS
(courseID SMALLINT NOT NULL,
sectionID SMALLINT NOT NULL,
teacherID SMALLINT,
num_students SMALLINT,
PRIMARY KEY(courseID,sectionID),
INDEX(courseID),
FOREIGN KEY(courseID)
REFERENCES COURSES(courseID)
ON UPDATE CASCADE ON DELETE CASCADE)TYPE=INNODB;
CREATE TABLE ENROLLS
(enrollmentID INT NOT NULL AUTO_INCREMENT,
courseID SMALLINT NOT NULL,
sectionID SMALLINT NOT NULL,
studentID SMALLINT NOT NULL,
grade SMALLINT,
PRIMARY KEY(enrollmentID),
INDEX(courseID),
REFERENCES COURSES(courseID)ON UPDATE CASCADE ON DELETE CASCADE,
INDEX(sectionID),
REFERENCES SECTIONS(sectionID)ON UPDATE CASCADE ON DELETE CASCADE,
INDEX(studentID),
REFERENCES STUDENTS(studentID)ON UPDATE CASCADE ON DELETE CASCADE)TYPE=INNODB;

Maru

 

[ericbrunson]

Try explicitly creating indexes on your primary keys. It's in the documentation Bastien posted.