From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Robert Haas" <Robert(dot)Haas(at)dyntek(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: complex referential integrity constraints |
Date: | 2007-02-18 18:08:21 |
Message-ID: | 8196.1171822101@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | elein | 2007-02-18 22:16:29 | Re: complex referential integrity constraints |
Previous Message | Guido Neitzer | 2007-02-18 18:07:57 | Re: Database performance comparison paper. |