Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Unanswered: What's wrong in this script???

    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.

  2. #2
    Join Date
    Apr 2004
    Posts
    6
    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?

  3. #3
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Originally posted by Schnaaf
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Originally posted by r937
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •