Postgres Pro
Downloads
Home   >   mailing lists

Thread: ALTER TABLE RENAME and primary key

ALTER TABLE RENAME and primary key

From
brian
Date:
I'd like to rename a table but am unsure how this will affect the
primary key. That is, the table currently being named 'service', it has
a pkey named 'service_id_pkey'. I'd like to change the table name to
'service_provider' (which would mean i'd get 'service_provider_id_pkey').

There's a second table that has a foreign key constraint on
service_id_pkey, so do i need to drop that constraint first, rename the
first table, then re-create the constraint using the new pkey name?

brian

Re: ALTER TABLE RENAME and primary key

From
"Raymond O'Donnell"
Date:
On 9 Dec 2006 at 18:22, brian wrote:

> I'd like to rename a table but am unsure how this will affect the
> primary key. That is, the table currently being named 'service', it has a
> pkey named 'service_id_pkey'. I'd like to change the table name to
> 'service_provider' (which would mean i'd get 'service_provider_id_pkey').

I just tried it on 8.2, and it seems that renaming the table doesn't
rename the primary key index - you have to do it explicitely.

> There's a second table that has a foreign key constraint on
> service_id_pkey, so do i need to drop that constraint first, rename the
> first table, then re-create the constraint using the new pkey name?

Without having tried it, I don't think so - my understanding is that
database objects are represented internally as OIDs, so renaming the
object changes only the name and not the OID.

--Ray.


----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------

Re: ALTER TABLE RENAME and primary key

From
Bruce Momjian
Date:
Raymond O'Donnell wrote:
> On 9 Dec 2006 at 18:22, brian wrote:
>
> > I'd like to rename a table but am unsure how this will affect the
> > primary key. That is, the table currently being named 'service', it has a
> > pkey named 'service_id_pkey'. I'd like to change the table name to
> > 'service_provider' (which would mean i'd get 'service_provider_id_pkey').
>
> I just tried it on 8.2, and it seems that renaming the table doesn't
> rename the primary key index - you have to do it explicitely.
>
> > There's a second table that has a foreign key constraint on
> > service_id_pkey, so do i need to drop that constraint first, rename the
> > first table, then re-create the constraint using the new pkey name?
>
> Without having tried it, I don't think so - my understanding is that
> database objects are represented internally as OIDs, so renaming the
> object changes only the name and not the OID.

We worked very hard to prevent renaming from affecting the behavior of
any objects.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +