Specialization multiple foreign key

Asked

Viewed 558 times

1

Hello, I am modeling a database and I came across the following problem: I have a table called team, which is a generalization to 4 specialized tables, where each will contain a different id, to know to which team each player belongs. Follow the image:inserir a descrição da imagem aqui

So when I test the logical model I get the following result:

inserir a descrição da imagem aqui

Three player tables, teams... and sponsor, and the problem is in the sponsor table that receives as a foreign key the id of the team, I do not want the sponsor to sponsor an 'n' team but the "times+time1+time2..." every team so I don’t know how to do it because when entering the physical model there is not a unique identifier that identifies all teams, only the id of each team: time1 id = 1 time2 id = 2 time3 id = 3 time4 id = 4

Physical model:

CREATE TABLE times+time1+time2+time3+time4 (
nome Text,
id int PRIMARY KEY,
f1 Text,
f2 Text,
f3 Text,
f4 Text
)

CREATE TABLE patrocinador (
nome Text,
valor Text,
cnpj int PRIMARY KEY,
id Text,
id Text,
FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

CREATE TABLE jogador (
cpf int PRIMARY KEY,
sobrenome Text,
nome Text,
lider Text,
time Text,
id int,
FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

Does anyone know how to fix it? Thanks in advance for your attention!

mysql database mysql-workbench

  • only you remove the foreign key from the Sponsors table, and add a foreign one in times+time2+... with sponsor ID, so you have a sponsor for all teams.

  • Your model is almost correct. The table times+time1+time2+time3+time4 has a primary key for each set of 4 times. When you declare times+time1+time2+time3+time4.id as a foreign key to patrocinador, you unite one sponsor to one set of teams and at the same time, one set of teams to several sponsors. The only mistake I see is that in the table patrocinador, has two fields named id.

1 answer

0

If you create tables this way:

Adding to FOREIGN KEY sponsor in the table of TIMES, you get to have a relationship UM-PARA-UM with sponsor, that is, each team will have a sponsor.

CREATE TABLE times+time1+time2+time3+time4 (
nome Text,
id int PRIMARY KEY,
f1 Text,
f2 Text,
f3 Text,
f4 Text,
ID_PATROCINADOR int NOT NULL,

CONSTRAINT PK_TIMES FOREIGN KEY (ID_PATROCINADOR) REFERENCES patrocinador (id)
)

In case you need to know, which TIME the PATROCINADORis sponsoring, will be needed by a FOREIGN KEYin sponsor also, which will be a relationship UM-PARA-MUITOS, that is, a sponsor can sponsor many teams.

CREATE TABLE patrocinador (
nome Text,
valor Text,
cnpj int PRIMARY KEY,
id Text,
id Text,

FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

CREATE TABLE jogador (
cpf int PRIMARY KEY,
sobrenome Text,
nome Text,
lider Text,
time Text,
id int,
FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

I’d like to give you some tips on how to create TABELAS:

  1. When creating table names, try the entity name in plural, for example: TIMES, PATROCINADORES, JOGADORES, etc..

When creating the CONSTRAINTS try their names to be easy for you to find when there is a problem, for example:

CREATE TABLE patrocinador (
nome Text,
valor Text,
cnpj int ,
id Text,
id Text,

CONSTRAINT PK_JOGADORES FOREIGN KEY (cnpj),
CONSTRAINT FK_PATROCINADOR_TIMES FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

CREATE TABLE jogador (
cpf int,
sobrenome Text,
nome Text,
lider Text,
time Text,
id int,
insira o código aqui
CONSTRAINT PK_JOGADORES PRIMARY KEY (ID),
CONSTRAINT FK_JOGADORES_TIMES FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

Create CONSTRAINT's giving their names facilitates in solving future errors.

Browser other questions tagged mysql database mysql-workbench

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