Re: Foreign keys for non-default datatypes

[ returning to a week-old thread... ]

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Thu, 23 Feb 2006, Tom Lane wrote:
>> Any thoughts about details? My feeling is that we should tie RI
>> semantics to btree opclasses, same as we have done for ORDER BY
>> and some other SQL constructs, but I don't have a concrete proposal
>> right offhand. The btree idea may not cover cross-type FKs anyway.

> ISTM that the btree opclass is too restrictive right now since I'm
> guessing we'd want to allow say int4 <-> numeric which I don't think is in
> either btree opclass, but I don't know if they're not there because it
> wasn't worth putting in or if there's a more fundamental reason.

I thought a bit more about this and realized that there's really a
fairly natural way to associate an FK constraint with a btree index
opclass. To wit, we already identify a unique index that the FK
constraint depends on to enforce uniqueness of the PK column --- and in
the current system, only btree indexes can be unique. So we can just
use the opclass(es) of that index. (If we ever add uniqueness support
to GiST, it would not be unreasonable to expect that the opclasses that
support uniqueness identify exactly which operator they think defines
equality, so we could still make it work for that.)

To handle the cross-type cases, I propose that we make two checks:

1. If the index opclass contains an exact operator for the case
"PKtype = FKtype", use that operator.

2. Otherwise, if there's an implicit coercion from the FK column
type to the PK column type, apply that coercion and use the opclass's
native equality operator.

If neither of these work, I think it'd be OK to fail (ie, reject
creation of the FK constraint). This would have the interesting
property that the current warning condition "FK constraint will require
costly sequential scans" would become an error case. I don't really
have a problem with that --- if there are any practically-useful cases
where people still get that warning, it means there are gaps we ought to
fill in the btree cross-type indexing support, not that we ought to go
out of our way to continue supporting a very inefficient mode of FK
operation. In any case, the current code is willing to try to enforce
FKs that use an "equality" operator that we have no hard evidence works
like equality at all, and that surely isn't a good idea.

Comments?

regards, tom lane