I have my own issue that forced me to use triggers. Given:
table users ( name login PK status etc. )
table status ( status relation label definition PK status, relation )
the relationship is: users.status = status.status AND status.relation = 'users';
This is a mathematically definable constraint, but there is no way in standard SQL to create an FK for it. This is one of the places I point to whenever we have the "SQL is imperfectly relational" discussion.
It'd be nice to say something like:
ALTER TABLE status ADD CONSTRAINT user_status_fk FOREIGN KEY (status) WHERE relation = 'users' REFERENCES users(status);
And the flip-side so you can have:
ALTER TABLE cheque_details ADD CONSTRAINT chq_trans_fk FOREIGN KEY (trans_id) REFERENCES transactions(trans_id) WHERE trans_type='CHQ';
Actually, since we can have a "unique index with where" this second form should be do-able shouldn't it?
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
