Thread: 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.