Re: REFERENCES this_table ( oid )

On Fri, 6 Dec 2002, Marques Johansson wrote:

> See the ERROR and comments below...
>
> create table photos (
> filename varchar[128] not null,
> filesize int default null,
> width int default null,
> height int default null,
> origPhoto oid default null references photos ( oid ),
> altDesc varchar[128] default null
> );
>
> psql:sid-sql:40: NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> psql:sid-sql:40: ERROR: UNIQUE constraint matching given keys for referenced table "photos" not found
>
> Am I not supposed to have self referencing 'reference' values? Or is the
> oid field invisible to this function? (Maybe because the table has yet to
> be created?) I don't see any reason why I shouldn't be able to make a
> self-referencing ref.

You can, but you can't reference to a column that's not defined unique
(which oid is not by default) like the error message says. At least in
7.3, it should work if you add a unique(oid) table constraint to the
definition (I think that may work in 7.2.x as well).

In general, referencing to oid may be a bad idea. You have to be careful
to make sure that you dump keeping oids the same, and if you ever have oid
rollover you may get uniqueness violations on the oid column since you
have to make it unique. You're probably better off adding a serial8 (or
serial) column to the table, making it the primary key and referencing
that.