mySQL - Cannot add foreign key constraint but also gives me a syntax error

Question

I am running my SQL code on https://www.db-fiddle.com/ and it is giving me multiples errors.

I am getting an error saying

Schema Error: Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

but shortly afterwards I am getting another error that says

Schema Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES department(dept_name) )' at line 7

however, I have looked at the syntax on that line multiple times and think it is fine, so I suspect the issue to be with me trying to add a foreign key constraint, but I am not sure.

Here is my full SQL code:

CREATE TABLE student(
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    tot_cred SMALLINT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );

CREATE TABLE department(
    dept_name VARCHAR(100) NOT NULL,
    building VARCHAR(100) NOT NULL,
    budget DECIMAL(10, 2) UNSIGNED,
    PRIMARY KEY(dept_name)
    );

CREATE TABLE advisor(
    s_id INT UNSIGNED NOT NULL,
    i_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (s_id),
    FOREIGN KEY (s_id) REFERENCES student(ID),
    FOREIGN KEY (i_id) REFERENCES instructor(ID)
    );

CREATE TABLE instructor(
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) UNSIGNED NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY dept_name REFERENCES department(dept_name)
    );

CREATE TABLE course(
    course_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    cedits SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );

CREATE TABLE prereq(
    course_id VARCHAR(100) NOT NULL,
    prereq_id VARCHAR(100) NOT NULL,
    PRIMARY KEY(course_id, prereq_id),
    FOREIGN KEY(course_id) REFERENCES course(course_id)
    );

CREATE TABLE takes(
    ID INT UNSIGNED,
    course_id INT UNSIGNED NOT NULL,
    sec_id VARCHAR(100) NOT NULL,
    semester VARCHAR(100) NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    grade DECIMAL(5, 2) UNSIGNED NOT NULL,
    PRIMARY KEY (ID, course_id, sec_id, semester, year)
    FOREIGN KEY (ID) REFERENCES student(ID),
    FOREIGN KEY (course_id) REFERENCES course(course_id), 
    FOREIGN KEY (sec_id) REFERENCES section(sec_id),
    FOREIGN KEY (semester) REFERENCES section(semester),
    FOREIGN KEY (year) REFERENCES section(year),
    );

CREATE TABLE section(
    course_id INT UNSIGNED NOT NULL,
    sec_id VARCHAR(100) NOT NULL,
    semester VARCHAR(100) NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    building VARCHAR(100) NOT NULL,
    room_no VARCHAR(100) NOT NULL,
    time_slot_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (course_id, sec_id, semester, year),
    FOREIGN KEY (course_id) REFERENCES section(course_id),
    FOREIGN KEY (building) REFERENCES classroom(building),
    FOREIGN KEY (room_no) REFERENCES classroom(room_no),
    FOREIGN KEY (time_slot_id) REFERENCES time_slot(time_slot_id)
    );

CREATE TABLE time_slot(
    time_slot_id INT UNSIGNED NOT NULL,
    day DATE NOT NULL,
    start_hour TIME NOT NULL,
    start_min TIME NOT NULL,
    end_hour TIME NOT NULL,
    end_min TIME NOT NULL,
    PRIMARY KEY (time_slot_id, day, start_hour, start_min)
    );

CREATE TABLE classroom(
    building VARCHAR(100) NOT NULL,
    room_no VARCHAR(100) NOT NULL,
    capacity SMALLINT NOT NULL,
    PRIMARY KEY (building, room_no)
    );

CREATE TABLE teaches(
    ID INT UNSIGNED,
    course_id INT UNSIGNED, 
    sec_id VARCHAR(100),
    semester VARCHAR(100),
    year INT UNSIGNED,
    PRIMARY KEY(ID, course_id, sec_id, semester, year),
    FOREIGN KEY(ID) REFERENCES instructor(ID),
    FOREIGN KEY(course_id) REFERENCES section(course_id),
    FOREIGN KEY(sec_id) REFERENCES section(sec_id),
    FOREIGN KEY(semester) REFERENCES section(semester),
    FOREIGN KEY(year) REFERENCES section(year)
    );

EDIT: I have now edited the position of my tables so that no foreign key references another column before it is defined in the sql file, but I am still getting the same errors albeit less frequently

Here is the new code:

CREATE TABLE time_slot(
    time_slot_id INT UNSIGNED NOT NULL,
    day DATE NOT NULL,
    start_hour TIME NOT NULL,
    start_min TIME NOT NULL,
    end_hour TIME NOT NULL,
    end_min TIME NOT NULL,
    PRIMARY KEY (time_slot_id, day, start_hour, start_min)
    );

CREATE TABLE department(
    dept_name VARCHAR(100) NOT NULL,
    building VARCHAR(100) NOT NULL,
    budget DECIMAL(10, 2) UNSIGNED,
    PRIMARY KEY(dept_name)
    );

CREATE TABLE student(
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    tot_cred SMALLINT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );


CREATE TABLE instructor(
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) UNSIGNED NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );

CREATE TABLE course(
    course_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    cedits SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );

CREATE TABLE prereq(
    course_id VARCHAR(100) NOT NULL,
    prereq_id VARCHAR(100) NOT NULL,
    PRIMARY KEY(course_id, prereq_id),
    FOREIGN KEY(course_id) REFERENCES course(course_id)
    );


CREATE TABLE classroom(
    building VARCHAR(100) NOT NULL,
    room_no VARCHAR(100) NOT NULL,
    capacity SMALLINT NOT NULL,
    PRIMARY KEY (building, room_no)
    );


CREATE TABLE section(
    course_id INT UNSIGNED NOT NULL,
    sec_id VARCHAR(100) NOT NULL,
    semester VARCHAR(100) NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    building VARCHAR(100) NOT NULL,
    room_no VARCHAR(100) NOT NULL,
    time_slot_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (course_id, sec_id, semester, year),
    FOREIGN KEY (course_id) REFERENCES section(course_id),
    FOREIGN KEY (building) REFERENCES classroom(building),
    FOREIGN KEY (room_no) REFERENCES classroom(room_no),
    FOREIGN KEY (time_slot_id) REFERENCES time_slot(time_slot_id)
    );

CREATE TABLE takes(
    ID INT UNSIGNED,
    course_id INT UNSIGNED NOT NULL,
    sec_id VARCHAR(100) NOT NULL,
    semester VARCHAR(100) NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    grade DECIMAL(5, 2) UNSIGNED NOT NULL,
    PRIMARY KEY (ID, course_id, sec_id, semester, year)
    FOREIGN KEY (ID) REFERENCES student(ID),
    FOREIGN KEY (course_id) REFERENCES course(course_id), 
    FOREIGN KEY (sec_id) REFERENCES section(sec_id),
    FOREIGN KEY (semester) REFERENCES section(semester),
    FOREIGN KEY (year) REFERENCES section(year),
    );



CREATE TABLE teaches(
    ID INT UNSIGNED,
    course_id INT UNSIGNED, 
    sec_id VARCHAR(100),
    semester VARCHAR(100),
    year INT UNSIGNED,
    PRIMARY KEY(ID, course_id, sec_id, semester, year),
    FOREIGN KEY(ID) REFERENCES instructor(ID),
    FOREIGN KEY(course_id) REFERENCES section(course_id),
    FOREIGN KEY(sec_id) REFERENCES section(sec_id),
    FOREIGN KEY(semester) REFERENCES section(semester),
    FOREIGN KEY(year) REFERENCES section(year)
    );

CREATE TABLE advisor(
    s_id INT UNSIGNED NOT NULL,
    i_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (s_id),
    FOREIGN KEY (s_id) REFERENCES student(ID),
    FOREIGN KEY (i_id) REFERENCES instructor(ID)
    );

`FOREIGN KEY dept_name REFERENCES department(dept_name)` should be `FOREIGN KEY (dept_name) REFERENCES department(dept_name)` — GMB, Nov 10 '20 at 21:51
There are many more issues. Tables are not created in the right sequence (children tables are created before parent tables). Foreign keys relationships are not correctly set up (there should be composite foreign keys to reference composite primary keys). You should probably split this in smaller pieces, so they can be iteratively fixed. — GMB, Nov 10 '20 at 22:00
I am not sure what you mean by " (there should be composite foreign keys to reference composite primary keys)". What is it that I need to change? — knowledge_seeker, Nov 10 '20 at 22:04
You might find this answer useful, it has a checklist of common mistakes related to foreign keys: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Nov 10 '20 at 22:42

score 1 · Answer 1 · answered Nov 10 '20 at 22:44

You have many errors.

First all colaumn and the references have to have the same datatype, everything else produces an error.

The next thing is every referenced table needs tio have an INDEX for every refernece

So for example

FOREIGN KEY(course_id) REFERENCES section(course_id),
FOREIGN KEY(sec_id) REFERENCES section(sec_id),
FOREIGN KEY(semester) REFERENCES section(semester),
FOREIGN KEY(year) REFERENCES section(year)

needs four singular indexes for every re4serence

but

 FOREIGN KEY (course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year)

has already a primary key index in section

If that is your first approach see that keep bith things in mind, and in time you will need to adapt your design, because of he gui ir demands

but you can do

CREATE TABLE time_slot(
    time_slot_id INT UNSIGNED NOT NULL,
    day DATE NOT NULL,
    start_hour TIME NOT NULL,
    start_min TIME NOT NULL,
    end_hour TIME NOT NULL,
    end_min TIME NOT NULL,
    PRIMARY KEY (time_slot_id, day, start_hour, start_min)
    );

CREATE TABLE department(
    dept_name VARCHAR(100) NOT NULL,
    building VARCHAR(100) NOT NULL,
    budget DECIMAL(10, 2) UNSIGNED,
    PRIMARY KEY(dept_name)
    );

CREATE TABLE student(
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    tot_cred SMALLINT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );


CREATE TABLE instructor(
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) UNSIGNED NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );

CREATE TABLE course(
    course_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    cedits SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
    );

CREATE TABLE prereq(
    course_id VARCHAR(100) NOT NULL,
    prereq_id VARCHAR(100) NOT NULL,
    PRIMARY KEY(course_id, prereq_id),
    FOREIGN KEY(course_id) REFERENCES course(course_id)
    );


CREATE TABLE classroom(
    building VARCHAR(100) NOT NULL,
    room_no VARCHAR(100) NOT NULL,
    capacity SMALLINT NOT NULL,
    PRIMARY KEY (building, room_no)
    );


CREATE TABLE section(
    course_id VARCHAR(100) NOT NULL,
    sec_id VARCHAR(100) NOT NULL,
    semester VARCHAR(100) NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    building VARCHAR(100) NOT NULL,
    room_no VARCHAR(100) NOT NULL,
    time_slot_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (course_id, sec_id, semester, year),
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    FOREIGN KEY (building,room_no) REFERENCES classroom(building,room_no),
    FOREIGN KEY (time_slot_id) REFERENCES time_slot(time_slot_id)
    );

CREATE TABLE takes(
    ID INT UNSIGNED,
    course_id VARCHAR(100) NOT NULL,
    sec_id VARCHAR(100) NOT NULL,
    semester VARCHAR(100) NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    grade DECIMAL(5, 2) UNSIGNED NOT NULL,
    PRIMARY KEY (ID, course_id, sec_id, semester, year),
    FOREIGN KEY (ID) REFERENCES student(ID),
    FOREIGN KEY (course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year)

    );



CREATE TABLE teaches(
    ID INT UNSIGNED,
    course_id VARCHAR(100), 
    sec_id VARCHAR(100),
    semester VARCHAR(100),
    year SMALLINT UNSIGNED,
    PRIMARY KEY(ID, course_id, sec_id, semester, year),
    FOREIGN KEY(ID) REFERENCES instructor(ID),
FOREIGN KEY (course_id,sec_id,semester,year) REFERENCES section(course_id,sec_id,semester,year)
    );

CREATE TABLE advisor(
    s_id INT UNSIGNED NOT NULL,
    i_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (s_id),
    FOREIGN KEY (s_id) REFERENCES student(ID),
    FOREIGN KEY (i_id) REFERENCES instructor(ID)
    );

mySQL - Cannot add foreign key constraint but also gives me a syntax error

1 Answers1