Re: complex referential integrity constraints - Mailing list pgsql-general

I partially agree:
If people CAN do stupid things, they are 'clever' enough to find a way
to actually do it. I've seen them destroy things, by just using a system
in a way it was not intended. They effectively found a way to blow away
the very thing that part was designed for.
But indeed, it's a lot of work, especially if the number of tables that
must be referenced increases. I'm a strong supporter for ensuring
consistency. Postgres has what it takes to do the job, but it doesn't
make my life a lot easier. But it seems to be as good as it gets
today...

Perhaps we should rather define a 'database' constraint in the order of:
"For every mauling, the attacking animal must be of the attacker type"
(in a computer understandable manner). From the set theory this should
be possible without too much problems, However doing so efficiently
might be slightly harder.
This might be a fun project and useful for the TODO list. At least it
makes it a lot easier (and maintanable) to enforce database-wide
constraints.

- Joris

>-----Original Message-----
>From: Robert Haas [mailto:Robert.Haas@dyntek.com]
>Sent: woensdag 21 februari 2007 3:37
>To: Joris Dobbelsteen; elein
>Cc: pgsql-general@postgresql.org
>Subject: RE: [GENERAL] complex referential integrity constraints
>
>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
>