I have problems with setting the reference on database table. I 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 is not working
It gives me this error:
Error code -5529, SQL state 42529: a UNIQUE constraint does not exist on referenced columns: TEAM in statement [ALTER TABLE player ADD FOREIGN KEY (team_id) REFERENCES team(id)]
As you can see, team table has composite primary key (club_id + id), the person references club through contract. Person has some common attributes for player and other staff types.
One club can have multiple teams. Employed person has to have a contract with a club. Player (is the specification of person) - if emplyed - can be assigned to one of the club's teams.
Is there better way to design my structure? I thought about excluding the club_id from team's primary key, but I would like to know if this is the only way. Thanks.
UPDATE 1
I would like to have the id as team identification only within the club, so multiple teams can have equal id as long as they belong to different clubs. Is it possible?
UPDATE 2 updated the naming convention as adviced by philip
Some business rules to better understand the structure:
- One club can have 1..n teams (Main squad, Reserve squad, Youth squad or Team A, Team B... only team can play match, not club)
- One team belongs to one club only
- A player is type of person (other types (staff) are scouts, coaches etc so they do not need to belong to specific team, just to the club, if employed)
- Person can have 0..1 contract with 1 club (that means he is employed or unemployed)
- Player (if employed) belongs to one team of the club
Now thinking about it - moving team_id from player to contract would solve my problem, and it could hold the condition "Player (if employed) belongs to one team of the club", but it would be redundant for other staff types. What do you think?
