In sql How to add a foreign key when the table has two primary keys?

Question

Okay basically, 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
foreign key.
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

Answer 1

You don't need Firstname to be part of the primary key. The AtheteID is all you need (I'm assuming it is unique). You can select against columns other than the primary key. You can also add a UNIQUE constraint on the name column (although in the real world, names aren't unique, especially first names).



You could also consider creating an index on the Firstname column. That will make searches on that column faster.

Answer 2

Are you saying that an athlete may play more than one position? If so then I would suggest creating another table and remove Position from the Athlete table. I don't see any reason for the first name being part of the primary key. To give the position a nice readable name you can add another small table...



create table AthletePositions (

AthleteID INTEGER NOT NULL,

PositionID CHAR(1) NOT NULL,

CONSTRAINT pk_AthletePositions PRIMARY KEY(AthleteID, PositionID)

CONSTRAINT fk_AthletePositions FOREIGN KEY (PositionID)

REFERENCES PositionNames(PositionID)

);



create table PositionNames (

PositionID CHAR(1) NOT NULL,

PositionName Varchar(12),

CONSTRAINT pk_PositionNames primary key(PositionID)

);



SELECT Firstname, Surname, PositionName

FROM Athlete a, AthletePositions ap, PositionNames pn

WHERE a.AthleteID = ap.AthleteID

AND ap.PositionID = pn.PositionID

AND Firstname='Fred'

AND Surname='Flintstone';

Answer 3

As Robin states, defining the foreign places key constraint won't immediately insert the linked rows into the different table for you - all it does is define the guideline that the cost could exist in the different table that the foreign places key references. in case you like something accomplished for you immediately, in regards to the only way you are able to do so is define a series off.