Re: complex referential integrity constraints - Mailing list pgsql-general

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@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!