ERROR 1215 (HY000): Cannot add foreign key constraint at creating a table

Question

I'm creating three tables in MySQL, those are programas, indicadores and seguimientoProgramado.

When I try to create the third one, I get this error:

ERROR 1215 (HY000): Cannot add foreign key constraint

Table programas:

create table programas(
    programa int not null,
    nombrePrograma varchar(60),
    primary key (programa));

Table indicadores:

create table indicadores(
    programa int not null,
    indicador varchar(10) not null,
    tipo enum('blanco','gris') not null,
    nombreIndicador varchar(300),
    periodicidad enum('anual','semestral','trimestral'),
    calculo enum('sumable','masAlto'),
    correlacion varchar(1000),
    comentarioTecnico varchar(1000),
    primary key (programa,indicador,tipo),
    foreign key (programa) references programas(programa)
    );

Table seguimientoProgramado:

create table seguimientoProgramado(
    programa int not null,
    indicador varchar(10) not null,
    trim1 int,
    trim2 int,
    trim3 int,
    trim4 int,
    avanceProgramado int,
    primary key (programa,indicador),
    foreign key (programa) references indicadores(programa),
    foreign key (indicador) references indicadores(indicador)
    );

What am I doing wrong?

Possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) The second highest rated answer covers your question. — Uueerdo, May 29 '18 at 19:18

score 0 · Accepted Answer · answered May 29 '18 at 20:03

From "13.1.18.6 Using FOREIGN KEY Constraints":

MySQL requires indexes on (...) referenced keys (...)

You have usable indexes on indicadores.programa, because primary key (programa,indicador,tipo) and foreign key (programa) references programas(programa) implicitly create indexes and programa is the first column of them.

But you have no usable index on indicadores.indicador. primary key (programa,indicador,tipo) has no effect here because indicador is the second column in the index.

To fix this put an index on indicadores.indicador. E.g. by altering your CREATE statement for indicadores:

create table indicadores(
    programa int not null,
    ...
    comentarioTecnico varchar(1000),
    primary key (programa,indicador,tipo),
    foreign key (programa) references programas(programa),
    key (indicador)
    );

Note key (indicador).

Thank you so much. I had solved that by adding `ENGINE=MyISAM` at the end of the create statement, but that meant to change the engine for all tables. With your help I don´t have to change the engine and helped me to understand better how indexes and keys work — Luis Huergo, May 31 '18 at 20:31

ERROR 1215 (HY000): Cannot add foreign key constraint at creating a table

1 Answers1