mysql - 错误: cannot add foreign key constraint

Question

我正在尝试向表添加外部约束:

ALTER TABLE owners_carparks2users
#  ADD CONSTRAINT FOREIGN KEY (`agglomeration_id`) REFERENCES `carparks_tmp` (`agglomeration_id`), <- this works fine
  ADD CONSTRAINT FOREIGN KEY (`carpark_id`) REFERENCES `carparks_tmp` (`carpark_id`)

但我收到错误:

Cannot add foreign key constraint

详细错误(显示引擎 INNODB 状态):

------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2014-05-21 11:40:12 127938000 Error in foreign key constraint of table carparks/#sql-2e5_6a: FOREIGN KEY (carpark_id) REFERENCES carparks_tmp (carpark_id): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.

还有我的 table :

CREATE TABLE carparks_tmp (
  `agglomeration_id` int(11) NOT NULL,
  `carpark_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`agglomeration_id`,`carpark_id`)
);

DROP TABLE IF EXISTS owners_carparks2users;
CREATE TABLE owners_carparks2users (
  `agglomeration_id` int(11) NOT NULL,
  `carpark_id` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL
);

任何帮助将不胜感激。

Answer 1

正如错误消息所示，您需要将 carpark_id 作为 carparks_tmp 上索引的第一列。

因此，您应该添加一个索引，例如:

  CREATE INDEX index_name
            ON carparks_tmp(carpark_id);

然后您就可以创建外键。