2 Foreign keys in the same key

Question

good afternoon! I am doing a Database exercise and came across a problem. I am registering a BD of an airline company. In one of the tables called flight need to register the code of an airport of origin and another code of a destination airport.

 CREATE TABLE IF NOT EXISTS  voo (
num_voo NUMERIC(3) NOT NULL,
cod_aeroporto_origem VARCHAR(3) NOT NULL,
cod_aeroporto_destino VARCHAR(3) NOT NULL,
vlr_distancia NUMERIC(6) NOT NULL,
vlr_preco NUMERIC(6,2) NOT NULL,
 CONSTRAINT pk_voo
 PRIMARY KEY(num_voo),

 CONSTRAINT fk_voo_aeroporto
 FOREIGN KEY(cod_aeroporto_origem, cod_aeroporto_destino)
 REFERENCES aeroporto(cod_aeroporto)


 ON DELETE RESTRICT
ON UPDATE RESTRICT
) engine="innodb";

    CREATE TABLE IF NOT EXISTS  aeroporto (
cod_aeroporto VARCHAR(3) NOT NULL,
nom_aeroporto VARCHAR(30) NOT NULL,
nom_cidade VARCHAR(25) NOT NULL,
 CONSTRAINT pk_aeroporto
 PRIMARY KEY(cod_aeroporto)

ON DELETE RESTRICT
ON UPDATE RESTRICT

) engine="innodb";

But when I put it in the database, I was given the following error: Wrong definition of the foreign key for 'fk_voo_airport': Key reference and table reference do not match

Answer 1

You are referencing only one field and Foreign key has 2 fields, I don’t know if this will work but try to create two fks, one for destination and one for arrival

     CONSTRAINT fk_voo_aeroporto_origem
         FOREIGN KEY(cod_aeroporto_origem)
         REFERENCES aeroporto(cod_aeroporto),

 CONSTRAINT fk_voo_aeroporto_destino
     FOREIGN KEY( cod_aeroporto_destino)
     REFERENCES aeroporto(cod_aeroporto)

2 Foreign keys in the same key

In fact you were creating a single key, composed of two fields, not two keys

Answer 2

Apparently the error was solved, I used a Constraint only for the two FK

CONSTRAINT fk_voo_aeroporto
 FOREIGN KEY(cod_aeroporto_origem)
 REFERENCES aeroporto(cod_aeroporto),
 FOREIGN KEY(cod_aeroporto_destino)
 REFERENCES aeroporto(cod_aeroporto)