MySQL缺少约束索引

Question

我正在尝试在 MySQL 中创建一些表，但遇到一个错误，我无法在此处找到答案。以下是我尝试创建的表格(为了便于阅读而缩进):

CREATE TABLE Journal (ISSN INT NOT NULL, ChiefEditor VARCHAR(20) NOT NULL,
 JournalTitle VARCHAR(20) NOT NULL, PRIMARY KEY(ISSN)); 

CREATE TABLE Volume (Year INT NOT NULL, ISSN INT NOT NULL, 
 PRIMARY KEY(Year, ISSN), FOREIGN KEY (ISSN) REFERENCES Journal(ISSN));

CREATE TABLE Edition (Month INT NOT NULL, Year INT NOT NULL, ISSN INT NOT NULL, 
 FirstPage INT, LastPage INT, PRIMARY KEY(Month, Year, ISSN), 
 FOREIGN KEY (Year, ISSN) REFERENCES Volume(Year, ISSN)); 

CREATE TABLE Article (FirstPage INT NOT NULL, LastPage INT NOT NULL, Month INT NOT NULL, 
 Year INT NOT NULL, ISSN INT NOT NULL, ArticleTitle VARCHAR(50), Abstract VARCHAR(1000), 
 PDFPath VARCHAR(50), PRIMARY KEY(FirstPage, LastPage, Month, Year, ISSN), 
 FOREIGN KEY (Month, Year, ISSN) REFERENCES Edition(Month, Year, ISSN));

CREATE TABLE User (Email VARCHAR(30) NOT NULL, Title VARCHAR(15), Forename VARCHAR(20), 
 Surname VARCHAR(30), University VARCHAR(30), Password VARCHAR(40),
 IsEditor BOOLEAN, IsAuthor BOOLEAN, IsReviewer BOOLEAN, PRIMARY KEY(Email));

CREATE TABLE ArticleAuthor (Email VARCHAR(30) NOT NULL, ISSN INT NOT NULL,
 Month INT NOT NULL, Year INT NOT NULL, FirstPage INT NOT NULL, LastPage INT NOT NULL,
 PRIMARY KEY(Email, ISSN, Month, Year, FirstPage, LastPage), FOREIGN KEY (Email)
 REFERENCES User(Email), FOREIGN KEY (ISSN, Month, Year, FirstPage, LastPage)
 REFERENCES Article(ISSN, Month, Year, FirstPage, LastPage));

当我运行这段代码时，MySQL 显示:

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'articleauthor_ibfk_2' in the referenced table 'article'

有问题的表似乎是 ArticleAuthor 和 Author，但为了以防万一，还包括了其他表。

我看过这里，大多数答案都表明要么主键/外键之间存在类型不匹配，要么我试图进行的引用是对不唯一的字段。但是，所有类型似乎都匹配，我相信我只是在引用肯定隐式唯一的主键字段？任何帮助将不胜感激。

Answer 1

文章的主键是:

PRIMARY KEY (FirstPage, LastPage, Month, Year, ISSN)

外键引用是:

FOREIGN KEY (ISSN, Month, Year, FirstPage, LastPage)

这些列的顺序不同。它们需要以相同的顺序排列。

也就是说，由 5 部分组成的复合主键似乎是数据库的噩梦。引入一个自动递增的 articleId 并使用:

FOREIGN KEY (articleId) REFERENCES articles (articleId)