sql - referencing part of the composite primary key -

i have problems setting reference on database table. have following structure:

create table club(     id integer not null,     name_short varchar(30),     name_full varchar(70) not null ); create unique index club_uix on club(id); alter table club add constraint club_pk primary key (id);  create table team(     id integer not null,     club_id integer not null,     team_name varchar(30) ); create unique index team_uix on team(id, club_id); alter table team add constraint team_pk primary key (id, club_id); alter table team add foreign key (club_id) references club(id);  create table person(     id integer not null,     first_name varchar(20),     last_name varchar(20) not null ); create unique index person_uix on person(id); alter table person add primary key (id);  create table contract(     person_id integer not null,     club_id integer not null,     wage integer ); create unique index contract_uix on contract(person_id); alter table contract add constraint contract_pk primary key (person_id); alter table contract add foreign key (club_id) references club(id); alter table contract add foreign key (person_id) references person(id);  create table player(     person_id integer not null,     team_id integer,     height smallint,     weight smallint  ); create unique index player_uix on player(person_id); alter table player add constraint player_pk primary key (person_id); alter table player add foreign key (person_id) references person(id); -- alter table player add foreign key (team_id) references team(id); --this not working 

it gives me error:

error code -5529, sql state 42529: unique constraint not exist on referenced columns: team in statement [alter table player add foreign key (team_id) references team(id)] 

as can see, team table has composite primary key (club_id + id), person references club through contract. person has common attributes player , other staff types.

one club can have multiple teams. employed person has have contract club. player (is specification of person) - if emplyed - can assigned 1 of club's teams.

is there better way design structure? thought excluding club_id team's primary key, know if way. thanks.

update 1

i have id team identification within club, multiple teams can have equal id long belong different clubs. possible?

update 2 updated naming convention adviced philip

some business rules better understand structure:

• one club can have 1..n teams (main squad, reserve squad, youth squad or team a, team b... team can play match, not club)
• one team belongs 1 club only
• a player type of person (other types (staff) scouts, coaches etc not need belong specific team, club, if employed)
• person can have 0..1 contract 1 club (that means employed or unemployed)
• player (if employed) belongs 1 team of club

now thinking - moving team_id player contract solve problem, , hold condition "player (if employed) belongs 1 team of club", redundant other staff types. think?

edit changed columns per question edit, incorporated comments, clarified

when subrow in 1 table has subrow in (referenced) table, inclusion dependency (ind). eg player team_id referencing team id (not key in team). when there ind and referenced subrow key, foreign key (fk). eg player person_id referencing person id (a key in person). in sql foreign key declaration says there ind , referenced columns unique. (ie declared either primary key or unique). (so means "foreign superkey".) in sql ideally declare ind (when there isn't fk) check constraint. (but dbmses not support check well). ie check player team_id in team projected on id. tried declare sql fk. fails because, error message says, team id not unique.

(notice here distinction commented between teams , team ids. because team id doesn't identify team, should speak of team identified team id , club. oop must distinguish not only between teams in world , team ids (some kind of string) in world also "team_id" pointer/reference values of programming language. commented id" bad name because team id alone doesn't identify team. use term used in world.)

but want stronger both player table team_id ind , player_id fk. ie check player team_id paired in club club_id paired in contract person_id person_id.

such complicated constraints unnecessary if design involved, say, roster(club_id,team_id,player_id) , didn't have team_id in player , didn't have contract either.

no, cannot "see" schema "person references club through contract". (and phrase poor way of expressing mean.) cannot tell tables , keys. every table holds rows satisfy statement parameterized column names; must give these statements. eg player: person [person_id] plays on team identified [team_id] & club. must give all business rules restrict situations can arise. eg: person can contracted @ 1 club. team belongs @ 1 club. user (nor we) cannot use database without statements , (nor we) cannot determine constraints without statements , business rules. write them out.