Обсуждение: Setting primary key on existing table
I would like to be able to redefine a field in a table to be a foreign reference -- however this field was initially defined as just "serial" and not "serial primary key". I tried to add that constraint to the table, but it didn't work. Do I need to copy out the table, drop it, and recreate it with this constraint in order for it to be a foreign key? Thanks -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Doug Silver Network Manager Urchin Software Corp. http://www.urchin.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver <dsilver@urchin.com> writes:
> I would like to be able to redefine a field in a table to be a foreign
> reference -- however this field was initially defined as just "serial" and
> not "serial primary key". I tried to add that constraint to the table, but
> it didn't work.
AFAIR, "serial" implies "unique not null" (at least in pre-7.3
releases), so your column is a primary key in all but name. All you'd
get from marking it as primary key is not having to mention the column
name when defining the foreign key reference. Is it good enough to
just write
... foo int REFERENCES mytable(mycol)
?
regards, tom lane
Hi Tom - Anyway to do this on an existing table or do I have to recreate it? Thanks. On Wednesday 25 September 2002 10:20 am, you wrote: > Doug Silver <dsilver@urchin.com> writes: > > I would like to be able to redefine a field in a table to be a foreign > > reference -- however this field was initially defined as just "serial" > > and not "serial primary key". I tried to add that constraint to the > > table, but it didn't work. > > AFAIR, "serial" implies "unique not null" (at least in pre-7.3 > releases), so your column is a primary key in all but name. All you'd > get from marking it as primary key is not having to mention the column > name when defining the foreign key reference. Is it good enough to > just write > ... foo int REFERENCES mytable(mycol) > ? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Doug Silver Network Manager Urchin Software Corp. http://www.urchin.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~