multiple foreign keys

[ernestclyde]

hi im making a database for my project
is it possible to have multiple foreign keys shown at the picture?

i tried to alter the tables and add foreign keys but i always get a #105 error, so i deleted all of my tables and will make a new set of tables.

[cereal]

Multiple foreign keys are possible, show your create statements and the full error message.

[ernestclyde]

CREATE TABLE user_list (
    employee_id varchar(225) ,
    password varchar(225),
    name varchar(225),
    department varchar(225),
    user_type varchar(225),
    PRIMARY KEY (name))     
 ENGINE=InnoDB;

CREATE TABLE students (
    student_id varchar(225) ,
    name varchar(225),
    gender varchar(225),
    course varchar(225),
    PRIMARY KEY (student_id))       
 ENGINE=InnoDB;

CREATE TABLE subjects (

    professor varchar(225),
    subject varchar(225),
    PRIMARY KEY (subject),
    FOREIGN KEY (professor) REFERENCES user_list(name))
  ENGINE=InnoDB;

    CREATE TABLE grade (

    subject varchar(225),
    professor varchar(225),
    student varchar(225),    
    student_id varchar(225),
    pq1 varchar(225),
    pq2 varchar(225),
    pcs varchar(225),
    pex varchar(225),
    mq1 varchar(225),
    mq2 varchar(225),
    mcs varchar(225),
    mex varchar(225),
    fq1 varchar(225),
    fq2 varchar(225),
    fcs varchar(225),
    fex varchar(225),
    ref int(10) unsigned NOT NULL,        
    PRIMARY KEY (ref),
    FOREIGN KEY (professor) REFERENCES user_list(name),
    FOREIGN KEY (student) REFERENCES student(name),
    foreign key (student_id) references student(student_id))
 ENGINE=InnoDB;

1005 - Can't create table 'csci14.grade' (errno: 150)

[ernestclyde]

where did i go wrong?

[ernestclyde]

btw i removed student from the subject table

[cereal]

There are two small errors here:

FOREIGN KEY (student) REFERENCES student(name),
foreign key (student_id) references student(student_id))

The name of the referenced table is students, so change it to:

FOREIGN KEY (student) REFERENCES students(name),
foreign key (student_id) references students(student_id))

The other problem is given by the first of these two constraints, in order to work you have to create a unique index for students.name, so:

CREATE TABLE students (
    student_id varchar(225),
    name varchar(225) unique,
    gender varchar(225),
    course varchar(225),
    PRIMARY KEY (student_id)
 ) ENGINE=InnoDB;

And it will work fine. Bye!

[ernestclyde]

thanks cereal :D

[ling_tj]

mark as solved, please!