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

[abzy1991]

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

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

I think you need to highlight your table code.

It's always common courtesy to do that if you want a member to help you.

The reason is very simple.

By highlighting the code.

Any member can read the code and read what you wrote and try to understand your issue that you are having.

To me it's hard to read.

[pritaeas]

Not very familiar with Oracle, so not sure if the first name constraint is causing this, but a Google search appears to confirm this. Make AthleteID the PK, and make the first name a unique index instead.