How use cascade delete for array field in PostgreSQL?

Question

How can i remove the value from array field? I need to delete the user id from the university table's user_id array field if exists when i delete any users. so can i do this by using delete cascade or any other method? please help me on this

Table:

CREATE TABLE IF NOT EXISTS users
 (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
 "userName" VARCHAR NOT NULL,
 active BOOLEAN NOT NULL);
CREATE TABLE IF NOT EXISTS university
 (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
 "universityName" VARCHAR NOT NULL,
 user_id integer[]);
User:
| _id | userName | active |
===========================
| 1 | Abc | true |
| 2 | vty | true |
| 7 | hyu | true |
| 9 | Agc | true |
University:
| _id | universityName | user_id |
=====================================
| 1 | HGV | {1,9} |
| 2 | CPC | {2} |

1) if i delete user 2 then entire row should get deleted from university 2) if i delete user 1 then only value 1 should be removed from the user_id field from university table

• Lennart - Slava Ukraini: May I suggest creating a table that represents the relationship between university and user that gets rid of the need for an array

Answer 1

First of all you need to create gin index on array field:

CREATE INDEX index_university_user_id ON university USING gin (user_id);

Then you need to add trigger to process deleting users:

CREATE FUNCTION trg_d_users() RETURNS TRIGGER AS $f$
BEGIN
 UPDATE university
 SET user_id = array_remove(user_id, OLD.id)
 WHERE user_id = OLD.id;
 RETURN OLD; 
END
$f$ LANGUAGE plpgsql;
CREATE TRIGGER trg_d_users BEFORE DELETE ON users FOR EACH ROW EXECUTE PROCEDURE trg_d_users()

If you want to remove university without users, you can add into function after UPDATE:

DELETE FROM university WHERE user_id = '{}';

And also I would reccomend to rename user_id field to user_ids. It very useful to see that field is array without checking schema.