sql - mysql. can't create schema when using composite UNIQUE key. ERROR 1005 (HY000): (errno: 150) -

i'm attempting create simple schema, see below. reason weird 150 error.i able narrow problem down unique key declaration: unique key (fk_im_savegroups_sgcode, sscode). if refactor declaration this: unique key (sscode) - works.

could explain me why can't use composite index?

drop database if exists test447; create database test447 charset utf8mb4 collate utf8mb4_unicode_ci; use test447;   create table im_savegroups (     id int unsigned not null auto_increment,     sgcode varchar(20) not null,     primary key (id),     unique key (sgcode) ) engine=innodb charset utf8mb4 collate utf8mb4_unicode_ci;  create table im_savespecs (     id int unsigned not null auto_increment,     fk_im_savegroups_sgcode varchar(20) not null,     sscode varchar(20) not null,     max_w int unsigned,     max_h int unsigned,     ratio_x int unsigned,     ratio_y int unsigned,     quality int unsigned,     format varchar(10),     rel_dir varchar(400),     is_retina tinyint unsigned default 0,     is_preferred tinyint unsigned default 0,     primary key (id),     foreign key (fk_im_savegroups_sgcode) references im_savegroups (sgcode) on delete cascade on update cascade,     unique key (fk_im_savegroups_sgcode, sscode) ) engine=innodb charset utf8mb4 collate utf8mb4_unicode_ci;  create table im_originals (     id int unsigned not null auto_increment,     fk_im_savespecs_sscode varchar(20) not null,     name varchar(255),     alt varchar(180),     filename varchar(64),     caption varchar(1024),     credit varchar(1024),     expires_at datetime,     created_at datetime,     updated_at datetime,     primary key (id),     foreign key (fk_im_savespecs_sscode) references im_savespecs (sscode) on delete cascade on update cascade ) engine=innodb charset utf8mb4 collate utf8mb4_unicode_ci; 

parent must have unique key on column

create table im_savegroups (     id int unsigned not null auto_increment,     sgcode varchar(20) not null,     primary key (id),     unique key (sgcode) ) engine=innodb; -- charset utf8mb4 collate utf8mb4_unicode_ci;   create table im_savespecs (     id int unsigned not null auto_increment,     fk_im_savegroups_sgcode varchar(20) not null,     sscode varchar(20) not null,     max_w int unsigned,     max_h int unsigned,     ratio_x int unsigned,     ratio_y int unsigned,     quality int unsigned,     format varchar(10),     rel_dir varchar(400),     is_retina tinyint unsigned default 0,     is_preferred tinyint unsigned default 0,     primary key (id),     foreign key (fk_im_savegroups_sgcode) references im_savegroups (sgcode) on delete cascade on update cascade,     unique key (fk_im_savegroups_sgcode, sscode),     unique key (sscode) -- ****************************** ) engine=innodb charset utf8mb4 collate utf8mb4_unicode_ci;   create table im_originals (     id int unsigned not null auto_increment,     fk_im_savespecs_sscode varchar(20) not null,     name varchar(255),     alt varchar(180),     filename varchar(64),     caption varchar(1024),     credit varchar(1024),     expires_at datetime,     created_at datetime,     updated_at datetime,     primary key (id),     constraint fk_orig_ss foreign key (`fk_im_savespecs_sscode`) references im_savespecs(sscode) on delete cascade on update cascade  ) engine=innodb charset utf8mb4 collate utf8mb4_unicode_ci; 

edit:

my biggest problems of this:

1) of column varchar(20) , composite on 2 of them, , op might want second composite. quite wide composites in book.

2) fk's should unique slim codes in code table (not shown or in existence), or pk's ints. speaks index optimization.

i make bold statement fk's ought go ints, unless such unbearable mental contemplation , code slim zipcode.