0

May I ask if these integrity constraints can be created over this schema using create script?

  1. club (klub) can have exactly one coach (trener)
  2. club can have just one owner (majitel)
  3. each member (člen) can be in just one club
  4. and a coach can set tactics (taktika) only for his club?

Is it possible to solve this with integrity constraints in the create script at all or are triggers used for it? I am a part of my create script.

Schema

enter image description here

Create script

CREATE TABLE club ( id_club SERIAL NOT NULL, nazev VARCHAR(256) NOT NULL, datum_zalozeni VARCHAR(256) NOT NULL ); ALTER TABLE club ADD CONSTRAINT pk_club PRIMARY KEY (id_club); ALTER TABLE club ADD CONSTRAINT uc_club_nazev UNIQUE (nazev); CREATE TABLE coach ( id_member INTEGER NOT NULL ); ALTER TABLE coach ADD CONSTRAINT pk_coach PRIMARY KEY (id_member); CREATE TABLE member ( id_member SERIAL NOT NULL, id_club INTEGER NOT NULL, jmeno VARCHAR(256) NOT NULL, prijmeni VARCHAR(256) NOT NULL, narodnost VARCHAR(256) NOT NULL, datum_narozeni VARCHAR(256) NOT NULL ); ALTER TABLE member ADD CONSTRAINT pk_member PRIMARY KEY (id_member); CREATE TABLE owner ( id_member INTEGER NOT NULL ); ALTER TABLE owner ADD CONSTRAINT pk_owner PRIMARY KEY (id_member); CREATE TABLE player ( id_member INTEGER NOT NULL, cislo VARCHAR(256) NOT NULL, post VARCHAR(256) NOT NULL ); ALTER TABLE player ADD CONSTRAINT pk_player PRIMARY KEY (id_member); ALTER TABLE coach ADD CONSTRAINT fk_coach_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE; ALTER TABLE member ADD CONSTRAINT fk_member_club FOREIGN KEY (id_club) REFERENCES club (id_club) ON DELETE CASCADE; ALTER TABLE owner ADD CONSTRAINT fk_owner_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE; ALTER TABLE player ADD CONSTRAINT fk_player_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE; enter code here enter code here
Improve this question
4
  • You write, that the club should have only one trener, but in your schema you have a trainer associated with clen only. clen also has a club assigned. The question do you want a constraint on clen table to restrict entering 2 members with the same club but different trainer, o do you allow adding new collumns to your tables (klub specifically). Commented May 13, 2021 at 7:44
  • But if I added a column to the club, I would still have to watch it somehow so that I wouldn't add a coach again. Commented May 13, 2021 at 12:24
  • I looked at the schema once more - I see trener has field id_clen. This should be interpreted as "coach is a member" ? Commented May 13, 2021 at 14:00
  • 1
    member can be player, coach or owner..... r any combination of functions, all three at once ... But there must not be in more teams ... and at the same time in each team there must be only one coach and one owner Commented May 13, 2021 at 19:36

1 Answer 1

Reset to default
1

There is no way to enforce the required constraints on current schema. What you need is a unique constraint, but unique constraints work on single table data, and you don't have a table that would contain a role of a member and a club information.

In order to achieve them you'll have to add new column with a role info to a clen table (member), or club info to the trener and majitel tables. Because a single member can have multiple roles, first solution is not an option.

Just adding a club would potentially cause data consistency problems (someone changes the member's club id, but not the coache's). To enforce the integrity I add foreign key on a pair (member_id, club_id).

The result is:

CREATE TABLE club ( id_club SERIAL NOT NULL, nazev VARCHAR(256) NOT NULL, datum_zalozeni VARCHAR(256) NOT NULL ); ALTER TABLE club ADD CONSTRAINT pk_club PRIMARY KEY (id_club); ALTER TABLE club ADD CONSTRAINT uc_club_nazev UNIQUE (nazev); CREATE TABLE coach ( id_member INTEGER NOT NULL ); ALTER TABLE coach ADD CONSTRAINT pk_coach PRIMARY KEY (id_member); CREATE TABLE member ( id_member SERIAL NOT NULL, id_club INTEGER NOT NULL, jmeno VARCHAR(256) NOT NULL, prijmeni VARCHAR(256) NOT NULL, narodnost VARCHAR(256) NOT NULL, datum_narozeni VARCHAR(256) NOT NULL ); ALTER TABLE member ADD CONSTRAINT pk_member PRIMARY KEY (id_member); CREATE TABLE owner ( id_member INTEGER NOT NULL ); ALTER TABLE owner ADD CONSTRAINT pk_owner PRIMARY KEY (id_member); CREATE TABLE player ( id_member INTEGER NOT NULL, cislo VARCHAR(256) NOT NULL, post VARCHAR(256) NOT NULL ); ALTER TABLE player ADD CONSTRAINT pk_player PRIMARY KEY (id_member); ALTER TABLE coach ADD CONSTRAINT fk_coach_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE; ALTER TABLE member ADD CONSTRAINT fk_member_club FOREIGN KEY (id_club) REFERENCES club (id_club) ON DELETE CASCADE; ALTER TABLE owner ADD CONSTRAINT fk_owner_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE; ALTER TABLE player ADD CONSTRAINT fk_player_member FOREIGN KEY (id_member) REFERENCES member (id_member) ON DELETE CASCADE; --We need a unique constraint on (id_member, id_club), to be able to use the pair in a foreign key. ALTER TABLE member ADD CONSTRAINT unq_member_and_club UNIQUE(id_member, id_club); --Req. 1: club (klub) can have exactly one coach (trener) ALTER TABLE coach ADD COLUMN id_club integer; ALTER TABLE coach ADD CONSTRAINT fk_coach_club FOREIGN KEY (id_club) REFERENCES club (id_club); ALTER TABLE coach ADD CONSTRAINT fk_coach_member_and_club FOREIGN KEY (id_member, id_club) REFERENCES member (id_member, id_club); ALTER TABLE coach ADD CONSTRAINT fk_single_coach_per_club UNIQUE(id_club); --Req. 2: club can have just one owner (majitel) ALTER TABLE owner ADD COLUMN id_club integer; ALTER TABLE owner ADD CONSTRAINT fk_owner_club FOREIGN KEY (id_club) REFERENCES club (id_club); ALTER TABLE owner ADD CONSTRAINT fk_owner_member_and_club FOREIGN KEY (id_member, id_club) REFERENCES member (id_member, id_club); ALTER TABLE owner ADD CONSTRAINT fk_single_owner_per_club UNIQUE(id_club);

You can test the script in db<>fiddle.

Please note, that I have added fk_coach_club and fk_owner_club foreign key constraints although they are not mandatory. fk_member_club, fk_owner_member_and_club and fk_coach_member_and_club together ensures the club_id values in owner and coach tables are always valid, but if feels natural for me to have these FK's.

Req. 3 each member (člen) can be in just one club Nothing needs to be changed in your schema, member has club attribute, there is no way to assign another club to a single member.

There is no special constraint for Req.4 a coach can set tactics (taktika) only for his club, because tactics is set by coach that is assigned to a single club, so there is no constraint required.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

May I ask exactly how ALTER TABLE member ADD CONSTRAINT unq_member_and_club UNIQUE(id_member, id_club); works? Does this really prevent one member from being in more than one team? For example, does this prevent a member with id = 2 from being in team 1 and at the same time in team 2?
For example, will it stop me from doing this? id_clen = 2 id_klub = 1; id_clen = 2 id_klub = 2; ?
unq_member_and_club does not ensure thant the member will not belong to several clubs - the structure of member table ensures that. The table has club_id field, so there is no way to enter 2 different values for single member. unq_member_and_club is required to use the key pair (member_id, club_id) in a foreign key. Foreign keys will not allow entering a value different from member.id_club into tables owner and coach. I've altered my answer based on your comments.