MySql错误150，不知道为什么

Question

我创建了 2 个表:

CREATE TABLE `Albums_On_Facebook` (
  `Album_Id` int(11) unsigned NOT NULL,
  `Facebook_Album_Id` bigint(20) unsigned NOT NULL,
  `Open_Time` datetime NOT NULL COMMENT 'Album open time',
  PRIMARY KEY (`Album_Id`,`Facebook_Album_Id`),
  CONSTRAINT `Album_Id_Onfacebook-Fkey` FOREIGN KEY (`Album_Id`) REFERENCES `Albums` (`Album_Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Pictures_In_Album` (
  `Facebook_Album_Id` bigint(20) unsigned NOT NULL,
  `Faceook_Picture_Id` bigint(20) unsigned NOT NULL,
  `Time_Taken` datetime NOT NULL COMMENT 'Time that the photo was taken',
  `Description` text CHARACTER SET latin1 COMMENT 'caption of the photo',
  `Source` text CHARACTER SET latin1 NOT NULL COMMENT 'Source of the photo',
  `Latitude` float(10,6) NOT NULL COMMENT 'Location of where the photo was taken',
  `Longitude` float(10,6) NOT NULL COMMENT 'Location of where the photo was taken',
  `Is_Best_Picture` bit(1) NOT NULL COMMENT 'Best picture or not',
  `Photographer_Facebook_Id` bigint(20) NOT NULL COMMENT 'User Facebook identity of the user',
  PRIMARY KEY (`Facebook_Album_Id`,`Faceook_Picture_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我正在尝试添加此限制:

ALTER TABLE `Pictures_In_Album`
ADD CONSTRAINT Facebook_Album_Id_PicInAlbum_fKey
FOREIGN KEY (`Facebook_Album_Id`)
REFERENCES `Albums_On_Facebook`(`Facebook_Album_Id`);

我收到此错误:

Can't create table 'PicoNew.#sql-1cc8_6d29' (errno: 150)

为什么？

Answer 1

您需要在 Albums_On_Facebook 表中为引用的列提供一个键。此列是主键的次要列。这还不够:

Using FOREIGN KEY Constraints

[...]

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

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. (emphasis by me)