Обсуждение: REFERENCES this_table ( oid )

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.

I would try creating the table first, then altering the field later - but
you still can't 'alter column' that way.  Should I just forget about using
the 'references' keyword?

--
Marques Johansson
postgresql@displague.com


You may my glories and my state dispose,
But not my griefs; still am I king of those.
        -- William Shakespeare, "Richard II"

Hello Marques,

Your problem is that the oid is not indexed.
Create an index on photos(oid) then add the constraint separately.

JLL


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.
>
> I would try creating the table first, then altering the field later - but
> you still can't 'alter column' that way.  Should I just forget about using
> the 'references' keyword?
>
> --
> Marques Johansson
> postgresql@displague.com
>
> You may my glories and my state dispose,
> But not my griefs; still am I king of those.
>                 -- William Shakespeare, "Richard II"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

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.