RE: OID as Primary Key

But why doesn't PostgreSQL complain when a create a unique index on oid? I
think it is quite confusing..


/Jonas B

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, March 22, 2001 5:59 PM
To: Jonas Bengtsson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] OID as Primary Key

On Thu, 22 Mar 2001, Jonas Bengtsson wrote:

> Hello,
> I have problems with using OID as PK. I have created a
> UNIQUE INDEX on the oid on a relation called CourseEvents.
> The relation that is supposed to have a FK to CourseEvents
> is CourseEventsForums and it has a field called ceid which
> has oid as dtatype.
> But when I try to create a FK to that table I get following
> error:
> ---
> PostgreSQL said: ERROR: UNIQUE constraint matching given
> keys for referenced table "courseevents" not found
> Your query:
> ALTER TABLE CourseEventForums ADD CONSTRAINT
> RefCourseEvents43
> FOREIGN KEY (ceid)
> REFERENCES CourseEvents(oid)
> ---
> What is wrong?

The message is a bit misleading.  Oid isn't a "user" column,
and currently you can only create references to user
columns.  Given the issues involved with oid (since it's
system wide it rolls over faster than a serial, you have
to make sure to dump with oids, the fact that you have
much less control over its value if you need to do
something), I'm not 100% sure that's a bad thing, although
IIRC references to oid are on the todo list.