Re: Literals in foreign key definitions

On 4 Feb 2010, at 20:34, Tim Landscheidt wrote:

> Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)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!