On 12/04/2013 11:25 AM, Robert Haas wrote:
On Tue, Dec 3, 2013 at 5:57 PM, Tom Dunstan <pg...@tomd.cc> wrote:
On 4 December 2013 01:24, Robert Haas <robertmh...@gmail.com> wrote:
Yeah, more or less, but the key is ensuring that it wouldn't let you
create the constraint in the first place if the partial index
specified *didn't* match the WHERE clause. For example, suppose the
partial index says WHERE parent_entity = 'event' but the constraint
definition is WHERE parent_event = 'somethingelse'. That ought to
fail, just as creating a regular foreign constraint will fail if
there's no matching unique index.
The where clause only applies to queries against the FK table, and we
don’t currently fail if there isn’t a matching index on the fk column
when creating a FK (I’ve been bitten by that before).
We fail if there isn’t a unique index on the referenced
table/column(s), but queries against that table on insert/update not
the FK table are unchanged (save that we don’t bother with them at all
if the where clause expression fails for the given tuple).
Oh. I misinterpreted what this feature was about, then. I thought it
was about restricting the reference to a subset of the *referenced*
table, but it seems to be about restricting the constraint to a subset
of the *referencing* table. I guess they're both useful, but the
syntax...
REFERENCES tab(col) WHERE (stuff)
...sure looks like the WHERE clause is syntactically associated with
the table being referenced. What would we do if we eventually wanted
to support both variants?
Well I guess we could say something like:
FOREIGN KEY (a-col) WHERE (a-condition) REFERENCES b(b-col) WHERE
(b-condition)
But it's somewhat ugly.
The case of restricting the allowed referent rows does look slightly
like a solution in search of a problem, but maybe that's just because I
haven't thought of a use for it yet.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
