MySql error 150, don't know why

Question

I created to 2 tables:

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;

i'm trying to add this contraint:

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

and I'm getting this error:

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

Why?

See http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 — ʰᵈˑ, Jul 30 '14 at 12:35
refer to http://stackoverflow.com/questions/16227199/mysql-errno-150 — SagarPPanchal, Jul 30 '14 at 12:36

score 1 · Accepted Answer · answered Jul 30 '14 at 12:41

You need a key in your Albums_On_Facebook table for the referenced column. This column is secondary of your PRIMARY KEY. That is not enough:

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)

MySql error 150, don't know why

1 Answers1