I have an issue and I would like to know how I should go about this.
I have two tables. Athlete and EachAth_Games
create table Athlete ( AthleteID INTEGER NOT NULL, Firstname varchar(10) NOT NULL, Surname varchar(10), Position varchar(1), TeamID INTEGER NOT NULL, CONSTRAINT pk_Athlete primary key(AthleteID, Firstname), CONSTRAINT Player_FOREIGN_KEY FOREIGN KEY (TeamID) REFERENCES Team (TeamID)); create table EachAth_Games ( EachAthID INTEGER NOT NULL, AthleteID INTEGER NOT NULL, CONSTRAINT pk_Each primary key(EachAthID), CONSTRAINT Each_FOREIGN_KEY FOREIGN KEY (AthleteID) REFERENCES Athlete (AthleteID));
On EachAth_Games, I cant add the AthleteID as a FOREIGN KEY, it gives an error of
"no matching unique or primary key for this column-list".
The reason for this error, maybe because on Athlete table I have TWO Primary keys (AthleteID, Firstname).
I really need both of these to be a primary key, but then I also need the EachAth_Games to have AthleteID as the
What shall I do, Please can someone give any suggestion.
One table has two primary keys and the other table needs a foreign key which has to be AthleteID.
Reasons for having FIRSTNAME as a primary key because I have to do a query of
For any given Athlete name, list the positions that they can play at them. The input parameter
(i.e. Athlete name) should be input at run time from the SQL prompt.
So that means I have to 'select Firstname (so firstname must be unique)
Also I was thinking if this doesn't work, then shall I make firstname as the Foreign key on the Athlete table?
I don't know if that will work because 'firstname' has to be unique, no duplicates.
Let me know anyone, suggestions/ideas. It would help a lot. Thanks