Hi,
How hard/generally useful would it be to allow the target of a foreign
key to be on a set of columns where only a subset of them actually have
a unique constraint. For example:
CREATE TABLE base ( id INTEGER NOT NULL PRIMARY KEY, type INTEGER NOT NULL );
CREATE TABLE type1info ( id INTEGER NOT NULL PRIMARY KEY, type INTEGER NOT NULL CHECK (type = 1), FOREIGN
KEY(id,type) REFERENCES base (id,type) );
It's possible to create a UNIQUE constraint on base(id,type) but it
seems redundant as the PRIMARY KEY constraint on id already ensures
uniqueness.
Somewhat independently, it would be nice to allow constant expressions
to be used on the left-hand-side of foreign key constraints. Allowing
them on the RHS seems nice for completeness, but appears completely
useless in practical terms. The second table would simply become:
CREATE TABLE type1info ( id INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY (id,1) REFERENCES base (id,type) );
Sam