Doubt with foreign key

Question

Doubt with foreign key

Navigation

#1 by (0 votes)

0

To register the games of a football league the following tables have been defined:

Time (
    id int primary key,
    nome char(20),
    pontos int, 
    nroVitorias, 
    golsMarcados int, 
    golsSofridos int )

Jogo (
    idTime1 int, 
    idTime2 int, 
    golsTime1 int, 
    golsTime2, 
    primary key (idTime1, idTime2) )

The Time table is preloaded with the data id and nome of all the teams that participate in the championship and with all the remaining zero values. The Games table is empty. Type the SQL commands to resolve the following requests:

The idTime1 and idTme2 columns should be set to foreign keys by referencing the Time table.

Type the commands to set these 2 foreign keys so that a team that already has some game registered can not be deleted and if the id of a team is changed, their games are preserved.

Is it possible to create a foreign key from a column in a table (Time), referencing two columns in another table (Games)?

Thank you.

mysql sql relacionamento chave-estrangeira

asked by anonymous 30.11.2018 / 19:48

1 answer

Google Analytics script error How to make a second instance of PHP run a system outside the Apache root folder?

score 0 · Answer 1

If I understand correctly you want to know how and if it is possible to relate two columns of a table with another column of another table, not allowing the exclusion of records that are already populated between these "relations".

In your attempt you are declaring foreign key in the same statement. But you should declare your new foreign key as follows:

Example:

ALTER TABLE Luta
    ADD CONSTRAINT id_fk_fighter1 FOREIGN KEY (lutador1) REFERENCES lutador (id),
    ADD CONSTRAINT id_fk_fighter2 FOREIGN KEY (lutador2) REFERENCES lutador (id);

With this the "fight" that in your case is Jogo will have references to fighter 1 and fighter 2.
All "fighters" who have a marked fight can not be excluded.