Re: complex referential integrity constraints

On Fri, Feb 16, 2007 at 09:58:56AM -0500, Robert Haas wrote:
> So, I have the following problem.
>
> Suppose you have two kinds of animals, sheep and wolves. Since they
> have very similar properties, you create a single table to hold both
> kinds of animals, and an animal_type table to specify the type of each
> animal:
>
> CREATE TABLE animal_type (
> id integer not null,
> name varchar(80) not null,
> primary key (id)
> );
> INSERT INTO animal_type VALUES (1, 'Sheep');
> INSERT INTO animal_type VALUES (2, 'Wolf');
>
> CREATE TABLE animal (
> id serial,
> type_id integer not null references animal_type (id),
> name varchar(80) not null,
> age integer not null,
> weight_in_pounds integer not null,
> primary key (id)
> );
>
> The animal_type table is more or less written in stone, but the animal
> table will be updated frequently. Now, let's suppose that we want to
> keep track of all of the cases where one animal is mauled by another
> animal:
>
> CREATE TABLE mauling (
> id serial,
> attacker_id integer not null references animal (id),
> victim_id integer not null references animal (id),
> attack_time timestamp not null,
> primary key (id)
> );
>
> The problem with this is that I have a very unsettled feeling about the
> foreign key constraints on this table. The victim_id constraint is
> fine, but the attacker_id constraint is really inadequate, because the
> attacker CAN NEVER BE A SHEEP. I really want a way to write a
> constraint that says that the attacker must be an animal, but
> specifically, a wolf.
>
> It would be really nice to be able to write:
>
> FOREIGN KEY (attacker_id, 2) REFERENCES animal (id, type_id)
>
> Or:
>
> CREATE UNIQUE INDEX wolves ON animal (id) WHERE type_id = 2;
> -- and then
> FOREIGN KEY (attacker_id) REFERENCES INDEX wolves
>
> ...but that's entirely speculative syntax. I don't think there's any
> easy way to do this. (Please tell me I'm wrong.)
>
> The problem really comes in when people start modifying the animal
> table. Every once in a while we have a case where we record something
> as a wolf, but it turns out to have been a sheep in wolf's clothing. In
> this case, we want to do something like this:
>
> UPDATE animal SET type_id = 1 WHERE id = 572;
>
> HOWEVER, this operation MUST NOT be allowed if it turns out there is a
> row in the mauling table where attacker_id = 572, because that would
> violate my integrity constraints that says that sheep do not maul.
>
> Any suggestions? I've thought about creating rules or triggers to check
> the conditions, but I'm scared that this could either (a) get really
> complicated when there are a lot more tables and constraints involved or
> (b) introduce race conditions.
>
> Thanks,
>
> ...Robert

I'd do something like this:

CREATE TABLE animal_type (
animal_name TEXT PRIMARY KEY,
CHECK(animal_name = trim(animal_name))
);

/* Only one of {Wolf,wolf} can be in the table. */

CREATE UNIQUE INDEX just_one_animal_name
ON animal_type(LOWER(animal_name));

CREATE TABLE predator (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);

CREATE TABLE prey (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);

CREATE TABLE mauling (
id SERIAL PRIMARY KEY,
attacker_id INTEGER NOT NULL REFERENCES predator (animal_type_id),
victim_id INTEGER NOT NULL REFERENCES prey (animal_type_id),
attack_time TIMESTAMP WITH TIME ZONE NOT NULL
);

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!