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@xxxxxxxxxxxxxxxxx] Sent: Friday, February 23, 2007 4:02 AM To: Robert Haas Cc: David Fetter; pgsql-general@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxx 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 //
