Literals in foreign key definitions - Mailing list pgsql-general

Hello all,

I was trying something on my 8.3 server that's a bit controversial, and I wasn't surprised it didn't work. I think it
wouldbe nice if it were possible though... 

The case at hand is that I have a table:

CREATE TABLE unitclass (
    name TEXT NOT NULL PRIMARY KEY,
    is_baseclass BOOLEAN NOT NULL DEFAULT true
);

That has a many-to-many relationship with:

CREATE TABLE unitclass_relation (
    base    text NOT NULL,
    exponent    int NOT NULL,
    derived    text NOT NULL,

    FOREIGN KEY (base, true) REFERENCES unitclass (name, is_baseclass),
    FOREIGN KEY (derived, false) REFERENCES unitclass(name, is_baseclass)
);

This does give an error (not unexpected):
ERROR:  syntax error at or near "true"
LINE 8:     FOREIGN KEY (base, true) REFERENCES unitclass (name...

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 be
madeto work. Or is this already in 8.4 or 8.5 or is this a can of worms? Does the SQL spec disallow it? 

Cheers,
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,4b6ad4b910441146016476!