Re: Literals in foreign key definitions

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

> Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:
>
>> [...]
>> Now the intent here is to restrict foreign keys referencing the base class to unitclass records that describe a
baseclassand 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
bemade 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
columnsthat tell something about the record itself, but it saves me two round-trips to the referred table on every
insert/update/deleteoperation 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
properintegrity - 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
probablya 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!