Re: Foreign keys for non-default datatypes

On Thu, Mar 02, 2006 at 08:05:59PM -0500, Tom Lane wrote:
> [ 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

Somewhat related to this issue is that you cannot declare a primary key
on creation of a table on a column which should use a separate operator
class.

The case in point is a subtype (domain) with a BTREE operator class.
I can of course create a separate unique index, however, if I use the
PRIMARY KEY syntax the op class of the data type is not recognized.

I'm still thinking about the proper way this should be handled.
If FKs will be able to recognize the opclass of the datatype, then the
primary key should also using the same code. But if you can only create
PK constraints on default BTREE opclasses then you can only create FKs on
default BTREE opclasses. What I'm saying is that the opclass needs to be
an option to PRIMARY KEY and FOREIGN KEY--whether implicitly in the code
or explicitly in the grammar. But as I said I'm thinking about it. Perhaps
there are other alternatives.

--elein
elein(at)varlena(dot)com