2 foreign key in one field

6

Good,

I have 3 tables:

  • Athlete

    • Id (Pk)
    • Name
  • AthleteF

    • Id (PK)
    • Name
    • NFed
  • Escorts

    • Id (PK)
    • Name
    • Type
    • Athlete (FK)

The Athlete field in the Escorts table is the Id of the Athlete table and the AthleteF .

How do I do in mySQL to join the tables?

Does anyone know?

    
asked by anonymous 22.06.2015 / 11:34

2 answers

3

Your design is wrong. Every federated athlete is an athlete; you want to do relational inheritance . Your design has to be something like

CREATE TABLE Atleta (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nome TEXT NOT NULL);

CREATE TABLE AtletaF (
    Id INT NOT NULL PRIMARY KEY,
    NFed TEXT NOT NULL,
    FOREIGN KEY (Id) REFERENCES Atleta (Id) ON DELETE CASCADE);

CREATE TABLE Acompanhantes (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nome TEXT NOT NULL,
    Tipo TEXT NOT NULL,
    Atleta INT NOT NULL,
    FOREIGN KEY (Atleta) REFERENCES Atleta (Id) ON DELETE CASCADE);

Having said that, this design has other issues too:

  • Can an athlete be associated with more than one federation throughout his or her life? If yes, its AtletaF table must have a reference to a new table, Federacao , and if it can be associated with more than one federation at the same time, AtletaF must have a composite primary key. / p>

  • I do not know what your companion concept is, but can a companion accompany more than one athlete? If so, you need to break this foreign key to make a many-many relationship, with an auxiliary table, say AcompanhanteAtleta , and move the Atleta FK.

  • Last but not least , one table is named Atleta and the other Acompanhantes . I'm in favor of naming tables in the singular, but I understand there are controversies - if you want to name everything in the plural OK, but be consistent.

22.06.2015 / 17:25
3

You can not create a foreign key that points to two different tables.

What you can do is denormalize, that is, join the Atleta and AtletaF tables and create some criteria in this new table to define whether the athlete is federated or not. I think the NFED field can do this work.

Another option would be the Acompanhantes table to have two foreign keys: one for Atleta and another for AtletaF .

    
22.06.2015 / 15:31