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
