Re: complex referential integrity constraints

> 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
> );

Just to add to David's idea, I would create two update-able views that joined animal to predator
and another for animal to prey.  This way, you only have to insert/update/delete from 1
update-able view rather than two tables.

Of course, I am still waiting for the future version of postgresql that will handle this
functionality seamlessly using table inheritance. :-)

Regards,
Richard Broersma Jr.