0

I would like to ask how in this part of conceptual scheme and create script I can create an integrity constraint / check that would set me that a club can have a maximum of one coach and just one owner. At the same time, but I admit that theoretically a member of the club can be both the player, the owner and the coach. Thank you in advance for any advice

enter image description here

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;
New contributor
martin129989451 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • 1
    The simplest way to accomplish this would be to have that data as part of the Club table. You can have multiple fields refer to the Member table. – matigo May 3 at 3:08

Your Answer

martin129989451 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.