I sort of think that this kind of stuff belongs in a separate table somehow. For example in this case I would want to: CREATE TABLE attack_probability ( attacker_type_id integer not null references animal_type (id), victim_type_id integer not null references animal_type (id), percent_chance integer not null, primary key (attacker_type_id, victim_type_id) ); ...and then declare that the CONSTRAINT on the "maulings" table is that if I look up the types of the attacker and victim, that pair of types must be present in the attack_probability table with percent_chance > 0. I guess my point here is that I think in your proposal you are letting domain-specific data creep into the schema. It's the job of the schema to enforce integrity constraints, but not to know specifically how things work. The fact (if it is a fact) that the chance of one type of animal attacking another can be captured as a probability (rather than, say, one probability for the day time and another probability for the night time, or one probability for each specific animal rather than each animal type, or I don't know what's going on and want to infer the probabilities from the data after I've gathered it) is domain-specific. I don't really want the information about attack probabilities (or whatever) to be something that's hardcoded in my schema; I want it to be part of the data in the schema, with the schema enforcing such constraints on that data as I may see fit to define. I don't want to have to select things out of system tables to find out attack probabilities. Also, as a practical matter, I suspect that such a setup would result in an absurdly complex constraint language. ...Robert -----Original Message----- From: Alban Hertroys [mailto:alban@xxxxxxxxxxxxxxxxx] Sent: Monday, February 26, 2007 4:15 AM To: Robert Haas Cc: David Fetter; pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] complex referential integrity constraints Robert Haas wrote: > 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. Basically I suggested to combine the constraint with a probability. If the probability of one animal attacking another is 0% it can't attack the other animal - that's a strict constraint. The other way around it can, and you'll also immediately know how likely that is to happen. An added bonus is that you can generalize certain constraints. If certain animals are less than - say 25% - likely to attack other certain other animals you could determine that the attacked animal is not in fact prey. An example would probably be wolves attacking other wolves (or predators in general). For relations that depend on an animal being prey, a constraint would be that this number be <25%. In this discussion it is also not entirely defined what attacking means. Is a ram defending his horde from wolves attacking (wolves)? I realise this all started from an analogy to a real problem, so most of this is probably not very relevant to your actual problem. No less interesting, though. -- 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 //
