Does anyone know how to make two two-column foreign keys from a single table?

Posted on

Question :

I tried the following, but I am receiving an error message.

Table goleo

mysql> create table goleo(
    -> id int,
    -> nombre varchar(30),
    -> goles int,
    -> primary key (nombre, id, goles));
Query OK, 0 rows affected (0.03 sec)

Table pichichi

mysql> create table pichichi(
    -> id int not null,
    -> nombre varchar(30) not null,
    -> goles int not null,
    -> foreign key (nombre) references goleo(nombre),
    -> foreign key (goles) references goleo(goles));

Error Message

ERROR 1822 (HY000): Failed to add the foreign key constraint. 
Missing index for constraint 'pichichi_ibfk_1' in the 
referenced table 'goleo'

Answer :

Las columnas de clave externa deben ser las mismas que las columnas de clave principal en la tabla a la que se hace referencia. Si la clave principal tiene tres columnas, entonces la clave externa debe tener tres columnas, en el mismo orden. No tres claves externas separadas.

The foreign key columns must be the same as the primary key columns in the referenced table. If the primary key has three columns, then the foreign key must have three columns, in the same order. Not three separate foreign keys.

As Bill says you need to declare the foreign key as

Foreign key (nombre, id, goles) references ...

I would recommend to name the constraint using

Constraint <name> Foreign key (nombre, id, goles) references ...

It will make it a lot easier to handle future modifications

Leave a Reply

Your email address will not be published. Required fields are marked *