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 would be 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 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?
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!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
