mysql - 如何从组合键创建外键？

Question

我想从主键创建外键并收到此错误“#1005 - 无法创建表”

这是使用的 DDL...

CREATE TABLE Invoice
(
InvoiceID SMALLINT,
TaskID SMALLINT,
FOREIGN KEY Invoice(InvoiceID) REFERENCES XTABLE(InvoiceID),
PRIMARY KEY (InvoiceID, TaskID)
);

Answer 1

确保 XTABLE 和 Invoice 中的键列标记为 PRIMARY KEY 和 NOT NULL。

CREATE TABLE XTABLE(InvoiceID SMALLINT NOT NULL, PRIMARY KEY(InvoiceID));

CREATE TABLE Invoice ( 
  InvoiceID SMALLINT NOT NULL, 
  TaskID SMALLINT NOT NULL,
  PRIMARY KEY (InvoiceID, TaskID),
  CONSTRAINT FOREIGN KEY Invoice(InvoiceID) REFERENCES XTABLE(InvoiceID)
);

SQL FIDDLE: http://sqlfiddle.com/#!2/5a56f