Re: REFERENCES this_table ( oid )

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