Mandatory participation of table in relationship in Postgres

Question

What is the best way to represent mandatory participation in Postgres? I used to do it with CHECK constraint or with INSERT and DELETE TRIGGER in Sybase. Postgres does not support subqueries in CHECK constraint and I cannot get triggers right.

I would like to find alternative to the following:

ALTER TABLE member
 ADD CONSTRAINT member_in_has_address
  CHECK (EXISTS (SELECT *
                 FROM address a
                 WHERE member_id = a.member_id));

"*cannot get triggers right*". So why don't you show us the problem with your trigger instead? — , Jan 03 '13 at 23:01
You can't do a query in a CHECK but you can call a function which does a query... — mu is too short, Jan 03 '13 at 23:28
It looks like you're trying for a reverse foreign key - that is, you want the _parent_ (master) table to verify that the _child_ (dependent) record exists; because `address` is supposed to have a copy of `member_id`. I'm not sure how you're expecting this to work: if you insert a record in `member`, it's going to fail if there's no corresponding record in `address`, but I'd expect a FK constraint on `address`, which would stop inserts if the matching `member_id` wasn't in `member` first... just what are you trying to accomplish here? — Clockwork-Muse, Jan 03 '13 at 23:40
Please add your version number of PostgreSQL, and state in plain English what you are trying to achieve. — Erwin Brandstetter, Jan 04 '13 at 00:19
Member has mandatory participation with address and I would like to enforced it. They taught us in school that the CHECK as above should be used and if this is not possible then DELETE and INSERT trigger should be used preventing from deleting or inserting row into member having no associated entry in address. Yes I had problems populating tables with triggers, because they are enforced immediately in Sybase, but no problem with CHECK constraint, because they are executed on subsequent entry or table involment in a query. — Radovan Luptak, Jan 04 '13 at 17:22
This is the way how I've done in before in Sybase: `CREATE TRIGGER mandatory_member_in_has_address AFTER DELETE ON member REFERENCING OLD AS old_member FOR EACH ROW WHEN (NOT EXISTS(SELECT* FROM member WHERE old_member.member_id = member.member_id)) BEGIN ATOMIC DECLARE invalid_member_row EXCEPTION FOR SQLSTATE '99999'; SIGNAL invalid_member_row; END;` — Radovan Luptak, Jan 04 '13 at 17:27
This is my Postgres version `CREATE TRIGGER mandatory_member_in_has_address_delete AFTER DELETE ON member FOR EACH ROW WHEN (OLD.member_id NOT IN (SELECT member_id FROM member)) EXECUTE PROCEDURE fire_exception();` — Radovan Luptak, Jan 04 '13 at 17:33

score 4 · Answer 1 · answered Jan 04 '13 at 01:59

4

I solve 1:m mandatory relationships using a deferrable constraint trigger.

The logic is:

Insert parent record; this schedules the trigger
Insert child record(s), at least one
commit

The trigger runs just before commit and can abort the commit by raising an exception if it doesn't like what it sees.

answered Jan 04 '13 at 01:59

Craig Ringer

307,061
76
688
778

I used to do deffered inserts which cope with problem above that a row cannot be inserted in a table, because has mandatory participation with child table. But this looks interesting, so I will look into this defferable constraint trigger. – Radovan Luptak Jan 04 '13 at 17:37

score 0 · Answer 2 · answered Jan 22 '13 at 20:00

To summarise, the problem can be solved by:

defining query in function and then calling it from CHECK constraint
defining function that throws exception and then calling it from trigger
defining deferrable constraint trigger

I am going to try all of them.

Mandatory participation of table in relationship in Postgres

2 Answers2