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@familiedobbelsteen.nl]
Sent: Monday, February 19, 2007 5:59 AM
To: elein; Robert Haas
Cc: pgsql-general@postgresql.org
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