Two Foreign Keys to the Same Field?

Question

Two Foreign Keys to the Same Field?

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have the doubt if it is valid to create rest tnego this table:

CREATE TABLE EQUIPO(
    id_equipo int not null,
    nombre varchar(45),
    encargado varchar (45),
    tel_encargado varchar(8),
    PRIMARY KEY (id_equipo)
);

and I need to create this other table:

CREATE TABLE PARTIDO(
    id_partido int,
    equipo_vis int,
    equipo_loc int,
    gol_visitante int,
    gol_local   int,
    id_cancha int,
    PRIMARY KEY (id_partido),
    FOREIGN KEY (equipo_vis,equipo_loc) REFERENCES EQUIPO(id_equipo),
    FOREIGN KEY (id_cancha) REFERENCES EQUIPO(id_cancha)
);

where "equipo_vis" and "equipo_loc" refer to "team_id" of the Team table, this is valid or how it can be restructured.

mysql sql

asked by Fredy Juarez 07.11.2018 в 18:19

source

2 answers

1

And why do not you make it more explicit?

...
FOREIGN KEY (equipo_vis) REFERENCES EQUIPO(id_equipo),
FOREIGN KEY (equipo_loc) REFERENCES EQUIPO(id_equipo),
...

I have no idea if how you put it works (I do not think so), but of course you can have two FKs in the same field in another table, separate them, nothing costs, works and remains very readable.

answered by 07.11.2018 в 18:28

Validation checkbox group with javascript or jquery How to add a summed and grouped mysql query to a table

score 1 · Accepted Answer

It is not valid because that SQL will be interpreted as defining a composite FK.

You can do it like this:

CREATE TABLE PARTIDO(
    id_partido int,
    equipo_vis int,
    equipo_loc int,
    gol_visitante int,
    gol_local   int,
    id_cancha int,
    PRIMARY KEY (id_partido),
    FOREIGN KEY (equipo_vis) REFERENCES EQUIPO(id_equipo),
    FOREIGN KEY (equipo_loc) REFERENCES EQUIPO(id_equipo),
    FOREIGN KEY (id_cancha) REFERENCES CANCHA(id_cancha) --ver nota
);

Note: By the way, the last FK would not be valid either because the TEAM table does not have a field id_cancha, I assume that you will surely have a table COURT