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.