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

Stephan Szabo <sszabo@megazone23.bigpanda.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