Обсуждение: Literals in foreign key definitions

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!

Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
>     FOREIGN KEY (base, true) REFERENCES unitclass (name, is_baseclass),
>     FOREIGN KEY (derived, false) REFERENCES unitclass(name, is_baseclass)

> 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? 

Yes.  FK constraints have to be columns vs. columns --- otherwise they
can't be represented in the information_schema views.

            regards, tom lane

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.

Tim

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!