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

I don't understand what a weighted constraint would mean.  Either the
attacker_id can be a wolf, or it can't.  Knowing that it is only 1%
likely over the long haul is insufficient to disallow any particular
transaction.

It's certainly true that the constraint as stated is insufficient to
guarantee that the table will contain good data.  For example if we
looked at the maulings table and wolves were always mauling other wolves
but never sheep, we would naturally want to dig into that a little more
and find out why they weren't picking easier targets.  But this is
neither here nor there, because NO constraint (foreign key, check, or
what have you) is ever strong enough to ensure that the data in a table
is completely clean.  At least as I understand it, the purpose of these
constraints is to allow us to write application code which relies on
certain basic invariants being true, i.e. so that we can join animal to
animal_type and not have to worry about rows dropping out because some
animals had an invalid type, or rows getting added because there are two
animal_type records with the same id.

Besides, the problem as stated is a proxy for some real problem which is
part of a non-zoological project the details of which (a) would take too
long to explain and (b) should probably not be posted to a public
mailing list.  :-)

So far, the best ideas I've seen have been:

(a) Tom Lane's idea of denormalizing by copying the animal type column
into the maulings table with ON UPDATE CASCADE, and then adding a CHECK
constraint on that column, and

(b) Creating a separate table called "wolf" and some triggers that
ensure that the wolf table will always contain the subset of IDs from
the animal table where the type_id is that of a wolf, with a foreign key
constraint from that id column back to animal with "on delete cascade".
This ensures that nobody can delete a wolf or change it into a sheep if
it has maulings, but permits it otherwise.

For what it's worth, I've adopted the latter solution for the present.
Unfortunately, it's too much work to do it everywhere it would be nice
to have, so I'm just doing it in some really critical cases and hoping
that the others don't break.

Thanks,

...Robert

-----Original Message-----
From: Alban Hertroys [mailto:alban@magproductions.nl]
Sent: Friday, February 23, 2007 4:02 AM
To: Robert Haas
Cc: David Fetter; pgsql-general@postgresql.org
Subject: Re: [GENERAL] complex referential integrity constraints

Robert Haas wrote:
> The idea here is that a wolf can attack a sheep, or a wolf can attack
> another wolf, but sheep can't attack anything.  I suppose I could list
> each wolf in both the predator and prey tables, but that seems a bit
> duplicative (and causes other problems).
>
> ...Robert

I'm quite certain a wolf is much more likely to attack a sheep than to
attack another wolf, and even more unlikely to attack for example a
lion. It seems to me that just the fact that it can isn't enough
information.

It looks like you need "weighted constraints"; there's 0 chance that a
sheep attacks a wolf, but there's >0 chance that a wolf attacks a sheep,
>0 chance it attacks a wolf and >0 chance it attacks a lion. The exact
numbers will vary, and I have absolutely no idea what they would be
like. It probably requires some kind of ranking system that adjusts
according to the known animals and their likelihood to attack eachother.

I'm pretty sure you can't get this done without defining some triggers.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //