On 4 Feb 2010, at 20:34, Tim Landscheidt wrote:
> Alban Hertroys <dal...@solfertje.student.utwente.nl> wrote:
>
>> [...]
>> Now the intent here is to restrict foreign keys referencing the base class
>> to unitclass records that describe a baseclass and to restrict foreign keys
>> referencing a derived class to unitclass records that do NOT describe a
>> baseclass.
>> Basically I'm trying to disallow derived classes to be derived of other
>> derived classes.
>
>> I can of course add a few triggers to force that constraint, but I think it
>> would be nice if the above syntax could be made to work. Or is this already
>> in 8.4 or 8.5 or is this a can of worms? Does the SQL spec disallow it?
>
> If you want to avoid triggers, another, simpler approach is
> to have a otherwise superfluous column "is_baseclass" with a
> default "TRUE" and constraints "NOT NULL" and
> "CHECK(is_baseclass)" and then use a "normal" foreign key
> constraint. I usually find that easier to read as it's more
> familiar.
Thanks, that's an interesting idea!
In my case I would need two such columns and name them a bit more elaborately
so that people don't mistake them for columns that tell something about the
record itself, but it saves me two round-trips to the referred table on every
insert/update/delete operation compared to using a trigger.
An added bonus, it gives me two columns to document on why the FK's are defined
in this way.
Of course it also adds two boolean values to every record in that table, but I
think that's not a bad price to pay for proper integrity - it's not like it'll
hold millions of rows anyway.
For the record, I ended up with:
CREATE TABLE unitclass_relation (
base text NOT NULL,
exponent int NOT NULL,
derived text NOT NULL,
base_is_baseclass boolean NOT NULL DEFAULT true CHECK (base_is_baseclass),
derived_is_baseclass boolean NOT NULL DEFAULT false CHECK (NOT
derived_is_baseclass),
FOREIGN KEY (base, base_is_baseclass) REFERENCES unitclass (name,
is_baseclass),
FOREIGN KEY (derived, derived_is_baseclass) REFERENCES unitclass(name,
is_baseclass)
);
This approach wouldn't scale too well if there'd be more than two different
values to constrain that FK on, but that's probably a pretty rare situation
anyway.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4b6bfe5210441240040803!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
