Re: oid not "UNIQUE" for use as FOREIGN KEY?

On Tue, 23 Oct 2001, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > On Tue, 23 Oct 2001, Ernesto Baschny wrote:
> >> Now, what is the problem? "oid" for sure should be an UNIQUE
> >> field in each table, doesn't it? Is it a bug?
>
> > To your last two questions, no and yes. Unless you place a unique index
> > on oid, it is not actually guaranteed unique (due to potential
> > wraparound), however the alter code also doesn't yet handle referencing to
> > non-user columns.
>
> As of yesterday, I think this is a bug, because you can now create a
> unique constraint on the OID column ... but I see it still doesn't work:

The yes was to it being a bug. I've always treated it as such since you
could make the "constraint" by making the index manually, just a low
priority one since such uses of oid should be discouraged anyway. :)

> regression=# CREATE TABLE gruppe (
> regression(# obergruppe_oid OID,
> regression(# name VARCHAR(32),
> regression(# unique(oid));
> NOTICE: CREATE TABLE/UNIQUE will create implicit index 'gruppe_oid_key' for table 'gruppe'
> CREATE
> regression=# ALTER TABLE gruppe ADD
> regression-# FOREIGN KEY (obergruppe_oid) REFERENCES gruppe (oid);
> NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> ERROR: UNIQUE constraint matching given keys for referenced table "gruppe" not found
> regression=#
>
> I wonder why the ALTER code doesn't notice the index on oid?

I think it's the use of rel_attrs[<number>] to get the attribute
name rather than the function you mentioned to Christopher (I
think) because I didn't know of its existance at the time. It should be
easy, I'll send a fix since it's now easier to run into.