Re: complex referential integrity constraints

On Mon, Feb 19, 2007 at 10:52:51AM -0800, Richard Broersma Jr wrote:
> > 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.

You could just do a rewrite RULE on predator and prey for each of
INSERT, UPDATE and DELETE that has a DO INSTEAD action that writes to
animal. This wouldn't handle COPY, though.

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

You mean writeable VIEWs? I think it would be nice to have some cases
handled, but there are several kinds of VIEWs I can think of where the
only sane way to write to them is to define the writing behavior on a
case-by-case basis.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!