Re: complex referential integrity constraints

"Robert Haas" <Robert(dot)Haas(at)dyntek(dot)com> 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