2 Foreign key in one field

Asked

Viewed 1,068 times

6

Good,

I have 3 tables:

  • Athlete

    • Id (Pk)
    • Name

  • Athlete

    • Id (PK)
    • Name
    • Nfed

  • Escorts

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

The field Athlete on the table Escorts is the Id table Athlete and table Athlete.

How do I do in mysql to join the tables?

Does anyone know?

mysql
  • 2

    What is the difference between Athlete and Athlete tables?

  • sorry miss a field I will edit now

  • Which tables do you want to join? What result do you want to get?

  • @Runo I want to join the athlete and athlete table id to the accompanying table, to show, later, the name of the escorts and the name of the athlete who will accompany.

  • @Runo is to mention that Id’s are auto_increment all.

  • 1

    You can’t create a Foreign key which refers to two tables.

  • Yes, but what is the relationship between the two Athlete and Athlete tables? How do you determine on the table whether the person is accompanying an athlete from the Athlete table or from the Athlete table? Why not join the two tables in one?

  • @ I have two pages HTML with form for both. If you enroll in the form for normal athlete, the guy table Chaperone is 0. If you subscribe to form for federated athlete the guy gets 1.

  • So what’s the table for Atleta @Brunogibellino?

  • Make a table only , ATHLETE , the NUMERO_FEDERACAO indicates if it is "federated".

  • @Motta the problem is that I need the id to start at 1 for the federated and the non-federated

  • To which @Brunogibellino?

  • @Jorgeb. Because if you’re a federate you go to one lane if you’re not a federate you go to another

  • and what that has to do with id?

  • 1

    Just have a table, the Atletaf, and check whether or not it is federated if(NFed==0)//não é federado

Show 10 more comments

2 answers

3

Cannot create a foreign key that points to two different tables.

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

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

3


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

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 problems too:

  • Can an athlete be associated with more than one federation throughout his life? If so, his table AtletaF need to have a reference to a new table, Federacao, and, if it can be associated with more than one federation at the same time, AtletaF has to have a composite primary key.

  • I don’t know what your idea of an escort is, but can an escort accompany more than one athlete? If yes, you need to break this foreign key to make a many-many relationship, with an auxiliary table, say, AcompanhanteAtleta, and move the FK Atleta thither.

  • Last but not least, a table is called Atleta and the other Acompanhantes. I am in favour of naming tables in the singular, but I understand that there are contradictions - if you want to name everything in the plural OK, but be consistent.

Browser other questions tagged mysql

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