[snaky]

That's a shame we have triggers as the only tool for this job, because e.g. CHECK constraint "cannot contain subqueries nor refer to variables other than columns of the current row"[1]. Triggers are very low-level thing actually, I'd consider them an assembler of RDBMS. So the questions like "Do the triggers and constraints outlined below actually cover all the bases, or is it still possible to add/modify data in such a way that the result would be inconsistent?"[2] are raised too often (usually they are not raised at all, and that's even worse). And the obvious answer[3] is application-level consistency checking - which is exactly me personally consider a shame for current state of RDBMS, especially in the light of Codd' ideas (referenced above), considering the more than half of century history of RDBMS industry.

[1] https://www.postgresql.org/docs/devel/static/sql-createtable...

[2] https://stackoverflow.com/questions/27191677/how-to-maintain...

[3] https://www.postgresql.org/message-id/m3iq57gcn9.fsf@passepa...