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
Best Answer
First of all you need to create gin index on array field:
Then you need to add trigger to process deleting users:
If you want to remove university without users, you can add into function after UPDATE:
And also I would reccomend to rename
user_id
field touser_ids
. It very useful to see that field is array without checking schema.