From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | foreign keys |
Date: | 2007-12-12 13:20:31 |
Message-ID: | 20071212132031.GC1676@frubble.xen.chris-lamb.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2007-12-12 13:22:03 | ScalarArrayOp advancing array keys |
Previous Message | Alvaro Herrera | 2007-12-12 12:20:01 | Re: Slow PITR restore |