CODEWITHSUNDEEP
mysql
Kolt Penny
Kolt Penny

Reputation: 164

MySQL error: "Foreign key constraint is incorrectly formed"

I have the following code is SQL and I double checked everything but I fail to find something wrong with it:

create table if not exists Agencia (
             nombreAgencia varchar(20) charset utf8 not null,
             pais varchar(20) charset utf8 not null,
             primary key (nombreAgencia)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table if not exists Mision (
             codigoMision varchar(20) charset utf8 not null,
             descripcion varchar(240) charset utf8 not null,
             primary key (codigoMision)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table if not exists Agente (
             nombreAgencia varchar(20) charset utf8 not null,
             idAgente int(10) not null,
             nombreClave varchar(100) charset utf8 not null,
             primary key (nombreAgencia,idAgente),
             foreign key (nombreAgencia) references Agencia(nombreAgencia)
             on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table if not exists Sede (
             nombreAgencia varchar(20) charset utf8 not null,
             nombreSede varchar(30) charset utf8 not null,
             primary key (nombreAgencia,nombreSede)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table if not exists Agente_t_Mision (
             nombreAgencia varchar(20) charset utf8 not null,
             idAgente int(10) not null,
             codigoMision varchar(20) charset utf8,
             primary key (nombreAgencia,idAgente),
             foreign key (nombreAgencia) references Agente(nombreAgencia)
             on delete cascade on update cascade,
             foreign key (idAgente) references Agente(idAgente)
             on delete cascade on update cascade,
             foreign key (codigoMision) references Mision(codigoMision)
             on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table if not exists Agente_p_Sede (
             nombreAgencia varchar(20) charset utf8 not null,
             idAgente int(10) not null,
             nombreSede varchar(20) charset utf8,
             primary key (nombreAgencia,idAgente),
             foreign key (nombreAgencia) references Agente(nombreAgencia)
             on delete cascade on update cascade,
             foreign key (idAgente) references Agente(idAgente)
             on delete cascade on update cascade,
             foreign key (nombreSede) references Sede(nombreSede)
             on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I get the following errors:

ERROR 1005 (HY000) at line 27 in file: '/home/kolt/Documents/IPN/6S/DDB/1P/tablas.sql': Can't create table `agencias`.`Agente_t_Mision` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 39 in file: '/home/kolt/Documents/IPN/6S/DDB/1P/tablas.sql': Can't create table `agencias`.`Agente_p_Sede` (errno: 150 "Foreign key constraint is incorrectly formed")

Everything is on point with the references, from the names to the types, charsets and engines and I'm still yet to find a good explanation for the errors.

Upvotes: 0

Views: 65

Answers (1)

Mittal Patel
Mittal Patel

Reputation: 2762

Since Agente has a composite primary key (nombreAgencia,idAgente), then any reference to it must also include both columns.

create table if not exists Agente_p_Sede (
         nombreAgencia varchar(20) charset utf8 not null,
         idAgente int(10) not null,
         nombreSede varchar(20) charset utf8,
         primary key (nombreAgencia,idAgente),
         foreign key (idAgente, nombreAgencia) references Agente(idAgente, nombreAgencia)
         on delete cascade on update cascade,
         foreign key (nombreSede) references Sede(nombreSede)
         on delete cascade on update cascade


create table if not exists Agente_t_Mision (
         nombreAgencia varchar(20) charset utf8 not null,
         idAgente int(10) not null,
         codigoMision varchar(20) charset utf8,
         primary key (nombreAgencia,idAgente),
         foreign key (idAgente, nombreAgencia) references Agente(idAgente, nombreAgencia)
         on delete cascade on update cascade,
         foreign key (codigoMision) references Mision(codigoMision)
         on delete cascade on update cascade

You cannot create a FK relationship to only parts of the PK on the target table - just can't do it.

Upvotes: 1

Related Questions