B9HVTuWd

1CREATE TABLE IF NOT EXISTS users
2    (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
3    "userName" VARCHAR NOT NULL,
4     active BOOLEAN NOT NULL);
5
6CREATE TABLE IF NOT EXISTS university
7    (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
8    "universityName" VARCHAR NOT NULL,
9    user_id integer[]);
10
11User:
12
13| _id | userName | active |
14===========================
15| 1   |  Abc     |   true |
16| 2   |  vty     |   true |
17| 7   |  hyu     |   true |
18| 9   |  Agc     |   true |
19
20University:
21
22| _id | universityName    | user_id |
23=====================================
24| 1   |  HGV              |  {1,9}  |
25| 2   |  CPC              |   {2}   |
26	
27CREATE INDEX index_university_user_id ON university USING gin (user_id);
28	
29CREATE FUNCTION trg_d_users() RETURNS TRIGGER AS $f$
30BEGIN
31    UPDATE university
32       SET user_id = array_remove(user_id, OLD.id)
33     WHERE user_id = OLD.id;
34    RETURN OLD; 
35END
36$f$ LANGUAGE plpgsql;
37
38CREATE TRIGGER trg_d_users BEFORE DELETE ON users FOR EACH ROW EXECUTE PROCEDURE trg_d_users()
39	
40DELETE FROM university WHERE user_id = '{}';