Skip site navigation (1) Skip section navigation (2)

Re: Recreate primary key without dropping foreign keys?

From: Frank Lanitz <frank(at)frank(dot)uvena(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Recreate primary key without dropping foreign keys?
Date: 2012-04-16 04:57:17
Message-ID: 20120416065717.70a31a6361ac5d7bdad416e8@frank.uvena.de (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
On Sun, 15 Apr 2012 18:41:05 -0600
Chris Ernst <cernst(at)zvelo(dot)com> wrote:

> Hi all,
> 
> In PostgreSQL 9.1.3, I have a few fairly large tables with bloated
> primary key indexes.  I'm trying to replace them using newly created
> unique indexes as outlined in the docs.  Something like:
> 
> CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors
> (dist_id); ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
>     ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX
> dist_id_temp_idx;
> 
> However, the initial drop of the primary key constraint fails because
> there are a whole bunch of foreign keys depending on it.
> 
> I've done some searching and haven't found a workable solution.  Is
> there any way to swap in the new index for the primary key constraint
> without dropping all dependent foreign keys?  Or am I pretty much
> stuck with dropping and recreating all of the foreign keys?

REINDEX is not working here? 

Cheers, 
Frank  
-- 
Frank Lanitz <frank(at)frank(dot)uvena(dot)de>

In response to

Responses

pgsql-admin by date

Next:From: Uwe BartelsDate: 2012-04-16 08:17:10
Subject: enterprisedb package and pam on debian
Previous:From: Chris ErnstDate: 2012-04-16 00:41:05
Subject: Recreate primary key without dropping foreign keys?

pgsql-general by date

Next:From: Thomas GuettlerDate: 2012-04-16 07:23:10
Subject: Re: Value to long for type ....: Columnname missing
Previous:From: Chris ErnstDate: 2012-04-16 00:41:05
Subject: Recreate primary key without dropping foreign keys?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group