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

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:

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?

regards, tom lane