Re: complex referential integrity constraints

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:

I kind of get the feeling that you'd want it like this:

CREATE TABLE predator (
...
) INHERITS animal;

And then put your foreign key constraints from predator to mauling.

You may want to be more accurate about what kind of animals sheep are as
well.

I haven't really given this much thought though, I'm just quickly
reading my mail before starting work ;)

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //