0

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.

Attachments Untitled.jpg 65.42 KB
3
Contributors
7
Replies
36
Views
3 Years
Discussion Span
Last Post by ling_tj
0
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)

Edited by ernestclyde: change code

0

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!

Edited by cereal

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.