CODEWITHSUNDEEP
sqlpostgresqltriggerscheck-constraints
Radovan Luptak
Radovan Luptak

Reputation: 291

Mandatory participation of table in relationship in Postgres

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));

Upvotes: 3

Views: 1174

Answers (2)

Radovan Luptak
Radovan Luptak

Reputation: 291

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.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324511

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.

Upvotes: 4

Related Questions