Yes, exactly. And while you might not care about all of those (e.g. I care about the first two but am not worried about the third one because I'm the only one who will ever update that table), writing multiple triggers to enforce each constraint of this type quickly gets old if there are even a few of them. It is exponentially harder to write a constraint of this type than it is to write a simple foreign key constraint. ...Robert -----Original Message----- From: Joris Dobbelsteen [mailto:Joris@xxxxxxxxxxxxxxxxxxxxx] Sent: Monday, February 19, 2007 5:59 AM To: elein; Robert Haas Cc: pgsql-general@xxxxxxxxxxxxxx Subject: RE: [GENERAL] complex referential integrity constraints >Why don't you add a field in animal_types that is boolean mauler. >Then you can add a trigger on the mauling table to raise an >error when the attacker_id is an animal type mauler. This is only partial. You need a lot more triggers to guarentee the constraints are enforced. Precisely you need to validate: * mauling on insert/update of attacker_id * animal on update of type_id * animal_type on update of your property Of course you need to think about the MVCC model, such that: Transaction 1 executes INSERT INTO mauling VALUES ('someattacker'), Transaction 2 executes UPDATE animal_type SET mauler = false WHERE name = 'someattacker', such that both transaction happen in parallel. This is perfectly possible and will make it possible to violate the constraint, UNLESS locking of the tuples is done correctly. These contraints are not trivial to implement (unfortunally). It would be great if they where. - Joris
