时间戳

首页 > 解决方案 > mySQL - 无法添加外键约束，但也给我一个语法错误

mysql - mySQL - 无法添加外键约束，但也给我一个语法错误

问题描述

我正在运行我的 SQL 代码https://www.db-fiddle.com/，它给了我多个错误。

我收到一条错误消息

架构错误：错误：ER_CANNOT_ADD_FOREIGN：无法添加外键约束

但不久之后我收到另一个错误，上面写着

架构错误：错误：ER_PARSE_ERROR：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 7 行的“REFERENCES department(dept_name))”附近使用正确的语法

但是，我多次查看该行的语法并认为它很好，所以我怀疑问题出在我试图添加外键约束，但我不确定。

这是我的完整 SQL 代码：

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)
    );

编辑： 我现在已经编辑了我的表的位置，以便在 sql 文件中定义之前没有外键引用另一列，但我仍然遇到相同的错误，尽管频率较低

这是新代码：

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)
    );

标签： mysqlforeign-keys

解决方案

你有很多错误。

首先，所有列和引用必须具有相同的数据类型，其他一切都会产生错误。

接下来的事情是每个被引用的表都需要为每个引用都有一个索引

所以例如

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)

每个 re4serence需要四个奇异索引

但

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

节中已有主键索引

如果这是您的第一种方法，请记住以下几点，并且您将需要及时调整您的设计，因为他 gui ir 的要求

但你可以

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)
    );

推荐阅读