Doubt with foreign key

Asked

Viewed 107 times

0

To register the matches of a football championship the following tables were 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 table Team is previously loaded with data id and nome of all the teams participating in the championship and with all the other zeroed values. The table Games is empty. Write SQL commands to resolve the following requests:

The columns idTime1 and idTme2 must be defined as foreign keys referencing the table Team.

Write the commands for setting these 2 foreign keys so that a team that already has a registered game cannot be deleted and if a team id is changed, their games are preserved.

It is possible for me to create a foreign key from a column in a table (Time), referencing two columns in another table (Games)?

Thank you.

mysql sql relationship foreign-key primary-key

  • can yes. if you are using phpmyadmim, the own does it very easily.

  • 1

    Add what you’ve tried to exercise.

  • @8bit ALTER TABLE Time ADD CONSTRAINT Timegame FOREIGN KEY (id) REFERENCES Game (idTime1, idTime2);

  • Right, what exactly do you mean by e se o id de um time for alterado, os seus jogos são preservados. ? Will the Team ID (Primary Key) be changed at some point? How will this be done ?

1 answer

0

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 instruction. But you must 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 that the "fight" that in your case is the Jogo will have references to fighter 1 and fighter 2.
  • All "fighters" who have a marked fight cannot be excluded.

Browser other questions tagged mysql sql relationship foreign-key primary-key

You are not signed in. Login or sign up in order to post.