mysql - 外键错误

Question

为什么外键让我头疼？
首先，我创建了数据库名称colorcode，然后创建了paper表，该表工作正常:

CREATE TABLE paper (
  paper_id int(20) NOT NULL,
  description VARCHAR(40) NOT NULL,
  paper_color VARCHAR(40) NOT NULL,
  PRIMARY KEY (paper_id, paper_color)
) ENGINE=InnoDB;

然后砖表

CREATE TABLE brick(
  brick_id int(20) NOT NULL,
  description varchar(40) NOT NULL,
  brick_color varchar (40) NOT NULL,
  PRIMARY KEY (brick_id),
  FOREIGN KEY (brick_color) REFERENCES paper(paper_color)
) ENGINE=InnoDB;

这不是=>

#1005 - 无法创建表“colorcode.brick”(错误号:150)

感谢您的帮助

Answer 1

create table paper (
  paper_id int(20) not null,
  description VARCHAR(40)not null,
  paper_color VARCHAR(40) NOT NULL,
  primary key (paper_id, paper_color),
  INDEX(`paper_color`)
)engine=InnoDB;

create table brick (
  brick_id int(20) not null,
  description varchar(40) not null,
  brick_color varchar (40) not null,
  primary key (brick_id), 

  CONSTRAINT foreign key (`brick_color`) REFERENCES  paper(`paper_color`)
)engine=InnoDB;

演示:http://sqlfiddle.com/#!2/316d9

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

“paper_color”必须是索引才能引用。

希望这有帮助。