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. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
