"Robert Haas" <Robert.Haas@xxxxxxxxxx> writes:
> ... 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 think the only way to do this in SQL is to denormalize a bit. If you
copy the animal_type field into the maulings table then you can apply a
check constraint there. So
FOREIGN KEY (attacker_id, attacker_type_id) REFERENCES animal (id, type_id)
ON UPDATE CASCADE
CHECK (attacker_type_id != 'sheep')
The thing that's still a bit annoying is that you'd have to hard-wire
the numerical code for SHEEP into the check constraint; you couldn't
really write it symbolically as I did above. Perhaps you should further
denormalize and keep real animal type names not codes in the animal
type table, thus
CREATE TABLE animal_type (
name varchar(80) primary key
);
CREATE TABLE animal (
id serial,
type varchar(80) references animal_type,
...
);
whereupon the maulings table also has real type names not IDs.
No doubt some relational-theory maven will come along and slap your
wrist for doing this, but he should first explain how to do it without
denormalization...
Also, I think what you've really done here is created a "poor man's
enum". There will probably be real enum types in PG 8.3, which would
offer a more efficient solution to the problem of representing animal
types.
regards, tom lane
