Here's the script:

CREATE TABLE person (
ID INTEGER(9) NOT NULL,
name VARCHAR(15) NULL,
initial CHAR(1) NULL,
father_lastname VARCHAR(30) NULL,
mother_lastname VARCHAR(30) NULL,
category SET('Student','Professor','Counselor','Director') NULL,
PRIMARY KEY(ID)
)
TYPE=InnoDB;

CREATE TABLE secuential (
secuential_ID INTEGER(4) NOT NULL,
name VARCHAR(20) NULL,
level VARCHAR(10) NULL,
PRIMARY KEY(secuential_ID)
)
TYPE=InnoDB;

CREATE TABLE course (
courseno VARCHAR(8 ) NOT NULL,
coursename VARCHAR(30) NULL,
description VARCHAR(60) NULL,
credits INTEGER(2) NULL,
meet_hours INTEGER(2) NULL,
PRIMARY KEY(courseno)
)
TYPE=InnoDB;

CREATE TABLE section (
courseno VARCHAR(8 ) NOT NULL,
section_no INTEGER(6) NOT NULL,
term_year INTEGER(4) NOT NULL,
instructor_ID INTEGER(9) NULL,
start_hour VARCHAR(7) NULL,
end_hour VARCHAR(7) NULL,
days VARCHAR(5) NULL,
PRIMARY KEY(courseno, section_no, term_year),
INDEX courseno(courseno),
FOREIGN KEY(courseno)
REFERENCES course(courseno)
ON DELETE CASCADE
ON UPDATE CASCADE
)
TYPE=InnoDB;

CREATE TABLE address (
ID INTEGER(9) NOT NULL,
address VARCHAR(30) NOT NULL,
city VARCHAR(15) NULL,
zip INTEGER(5) NULL,
PRIMARY KEY(ID),
FOREIGN KEY(ID)
REFERENCES person(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
TYPE=InnoDB;

CREATE TABLE email (
ID INTEGER(9) NOT NULL,
email VARCHAR(50) NOT NULL,
PRIMARY KEY(ID),
FOREIGN KEY(ID)
REFERENCES person(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
TYPE=InnoDB;

CREATE TABLE telephone (
ID INTEGER(9) NOT NULL,
number INTEGER(10) NULL,
category SET('Celular','Home','Work','Other') NULL,
notes VARCHAR(60) NULL,
PRIMARY KEY(ID),
FOREIGN KEY(ID)
REFERENCES person(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
TYPE=InnoDB;

CREATE TABLE secuential_course (
courseno VARCHAR(8 ) NOT NULL,
secuential_ID INTEGER(4) NOT NULL,
PRIMARY KEY(courseno, secuential_ID),
INDEX Course(courseno),
INDEX Secuential(secuential_ID),
FOREIGN KEY(secuential_ID)
REFERENCES secuential(secuential_ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(courseno)
REFERENCES course(courseno)
ON DELETE CASCADE
ON UPDATE CASCADE
)
TYPE=InnoDB;

CREATE TABLE person_has_secuential (
ID INTEGER(9) NOT NULL,
secuential_ID INTEGER(4) NOT NULL,
PRIMARY KEY(ID, secuential_ID),
INDEX Person(ID),
INDEX Secuential(secuential_ID),
FOREIGN KEY(ID)
REFERENCES person(ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(secuential_ID)
REFERENCES secuential(secuential_ID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
TYPE=InnoDB;

CREATE TABLE Record (
ID INTEGER(9) NOT NULL,
term_year INTEGER(4) NOT NULL,
section_no INTEGER(6) NOT NULL,
courseno VARCHAR(8 ) NOT NULL,
last_attendance DATE NULL,
grade ENUM('A','B','C','D','F','W','P','NP') NULL,
PRIMARY KEY(ID, term_year, section_no, courseno),
INDEX Person(ID),
INDEX Section(term_year, section_no, courseno),
FOREIGN KEY(ID)
REFERENCES person(ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(courseno, section_no, term_year)
REFERENCES section(courseno, section_no, term_year)
ON DELETE CASCADE
ON UPDATE CASCADE
)
TYPE=InnoDB;


When i run the script on MySQL it says error no 150, famous for lack of indexes, when creating table Record. But it has them! I've studied the design various times and with partners and it looks correct to us. Any idea? Thanx at least for reading.

I was able to copy paste your script in MySQL 4.0.18 and it finished without complaining.

Maybe it has something to do with the tabletype INODB. Is your mysql server configured with INODB support?

As u see in the script all databases are INNODB and have no complains, the only prob it gives is with the multi-field PK relation between Section and record. I need to check MySQL version to see if it's diffrent.

where you define the index called Section in the Record table, try defining it with the columns listed in the same order as they are in the foreign key

you have

INDEX Section(term_year, section_no, courseno),

try

INDEX Section(courseno, section_no, term_year),

this will now match the foreign key definition

I also noticed the same thing. I tried that option long ago but the error is still there...