GExHYY6s

1CREATE TABLE MEMBER
2( MEMBER_ID INT NOT NULL PRIMARY KEY -- ID is to vague IMO
3, MEMBER_TYPE CHAR(1) NOT NULL
4, ...
5, CONSTRAINT <name> CHECK (MEMBER_TYPE IN ('R','M','P'))
6) ;
7	
8ALTER TABLE MEMBER ADD CONSTRAINT <name> UNIQUE (MEMBER_ID, MEMBER_TYPE);
9	
10ALTER TABLE REFEREE ADD COLUMN MEMBER_TYPE CHAR(1) DEFAULT 'R' NOT NULL;
11ALTER TABLE REFEREE ADD CONSTRAINT <name> CHECK (MEMBER_TYPE = 'R');
12ALTER TABLE REFEREE ADD CONSTRAINT <name> 
13     FOREIGN KEY (MEMBER_ID, MEMBER_TYPE)
14     REFERENCES MEMBER (MEMBER_ID, MEMBER_TYPE);
15	
16CREATE schema football ;
17
18CREATE TABLE football.member
19(
20    member_id integer PRIMARY KEY,
21    name text,
22    surname text
23) ;
24
25CREATE TABLE football.referee
26(
27    member_id integer PRIMARY KEY REFERENCES football.member(member_id),
28    practice text,
29    league text
30) ;
31
32CREATE TABLE football.management
33(
34    member_id integer PRIMARY KEY REFERENCES football.member(member_id),
35    practice text,
36    club_id integer /* REFERENCES club(club_id) */
37) ;
38
39CREATE TABLE football.player
40(
41    member_id integer PRIMARY KEY REFERENCES football.member(member_id),
42    number integer,
43    club_id integer /* REFERENCES club(club_id) */
44) ;
45	
46CREATE OR REPLACE FUNCTION football.check_referee_only() RETURNS trigger AS
47$BODY$
48BEGIN
49    -- Check that referee is not a manager
50    if EXISTS (SELECT * FROM football.management x 
51                WHERE x.member_id = new.member_id) THEN
52        RAISE EXCEPTION 'Member % is already a Manager. Cannot also be a Referee', new.member_id;
53    END IF;
54
55    -- Check that referee is not a player
56    if EXISTS (SELECT * FROM football.player     x 
57                WHERE x.member_id = new.member_id) THEN
58        RAISE EXCEPTION 'Member % is already a Player. Cannot also be a Referee', new.member_id;
59    END IF;
60
61    -- Nothing wrong... just return
62    RETURN new ;
63END ;$BODY$
64LANGUAGE plpgsql VOLATILE ;
65
66-- You should also define equivalent two functions 
67-- for the other two roles (player and manager)
68	
69CREATE TRIGGER trg_check_referee_only 
70   BEFORE INSERT OR UPDATE OF member_id
71   ON football.referee FOR EACH ROW
72   EXECUTE PROCEDURE football.check_referee_only();
73
74-- You should also define equivalent two triggers 
75-- for the other two roles (player and manager)
76	
77-- We create one member
78INSERT INTO football.member(member_id, name, surname) VALUES (1, 'John', 'Doe') ;
79
80-- We make it a player...
81INSERT INTO football.player(member_id, number, club_id) VALUES (1, 12, 12345) ;
82
83-- If we try to make it a referee as well => an exception will occur
84INSERT INTO football.referee(member_id, practice, league) VALUES (1, 'some practice', 'some league') ;
85	
86ERROR: Member 1 is already a Player. Cannot also be a Referee
87SQL state: P0001
88Context: PL/pgSQL function football.check_referee_only() line 10 at RAISE