时间戳

首页 > 解决方案 > 添加外键失败

mysql - 添加外键失败

问题描述

我正在尝试创建两个表

CREATE TABLE Submissions
(
  quiz_ID INT(2) UNSIGNED,
  quiz_duration TIME,
  student_ID INT(2) UNSIGNED,
  attempt_date date,
  PRIMARY KEY (quiz_ID, student_ID)
);

和

CREATE TABLE Students
(
  student_ID INT(2) UNSIGNED,
  student_name VARCHAR(100),
  PRIMARY KEY (student_ID),
  FOREIGN KEY (student_ID) REFERENCES Submissions(student_ID)
);

但是，当我尝试创建第二个表时，它给了我一个错误

ERROR 1822 (HY000): 添加外键约束失败。引用表“提交”中的约束“students_ibfk_1”缺少索引

我如何解决它？

标签： mysqlsqldatabasedatabase-design

解决方案

您的外键顺序错误。submissions应该有外键约束。所以：

CREATE TABLE Submissions (
    quiz_ID INT(2) UNSIGNED,
    quiz_duration TIME,
    student_ID INT(2) UNSIGNED,
    attempt_date date,
    PRIMARY KEY (quiz_ID, student_ID),
    CONSTRAINT fk_subsmissions_student_id FOREIGN KEY (student_id) REFERENCES students(student_id)
);

当然，Students需要在定义外键之前创建表。

推荐阅读