Re: Adding another primary key to a populated table

Hi

Yes...  It's the only solution I know will work, but this is a core
table on a live system, and I haven't done this before.

Does anyone have a proven script they could share?



On Fri, 2006-01-06 at 08:22 -0700, Guy Fraser wrote:
> Have you considered dumping the data, dropping the table and
> building the replacement table with the correct properties
> then repopulating the table with the dumped data?
>
> On Thu, 2006-05-01 at 23:02 -0800, Daniel Kunkel wrote:
> > Why do I want to include 6 fields in the primary key?
> >
> > Good question...  I don't know. It's a requirement of OFBiz, a really
> > awesome ERP/CRM/Accounting/ECommerce system.
> >
> > I'm upgrading the software which requires it, and need to upgrade the
> > database to match.
> >
> > Once I find out, I'll publish the solution in the OFBiz forums and Wiki
> > so others won't come knocking.
> >
> > Thanks
> >
> > Daniel
> >
> > On Thu, 2006-01-05 at 22:44 -0800, Aaron Koning wrote:
> > > Are you trying to create a primary key composed of 6 fields? What is
> > > the result you want to achieve with the constraint? If you just want
> > > UNIQUE, NOT NULL values in a field, you can achieve that without
> > > creating a primary key.
> > >
> > > Aaron
> > >
> > > On 1/5/06, Daniel Kunkel <DanielKunkel@biowaves.com> wrote:
> > >         Hi
> > >
> > >         It makes sense that I can't have more than 1 primary key.
> > >
> > >         Postgres was trying to create another primary key instead of
> > >         modify the
> > >         existing primary key.
> > >
> > >         So...
> > >
> > >         As I understand it, a table does not always have to have a
> > >         primary key
> > >         defined.
> > >
> > >         Would it work to first delete/drop the primary key, then
> > >         recreate the
> > >         primary key on all 6 columns.
> > >
> > >         ALTER TABLE product_price DROP CONSTRAINT  product_price_pkey;
> > >
> > >         I tried this, but it doesn't seem to work...  If I look at the
> > >         table
> > >         from pgAdmin, it is still there, reindexable, I can't add a
> > >         new primary
> > >         key, etc.  But if I try to run the above command twice, it
> > >         says it's
> > >         already been removed.
> > >
> > >         --
> > >
> > >         Just for the record...  the error message I got was:
> > >
> > >         ERROR:  ALTER TABLE / PRIMARY KEY multiple primary keys for
> > >         table
> > >         'product_price' are not allowed
> > >
> > >
> > >         On Fri, 2006-01-06 at 05:19 +0000, Andrew - Supernews wrote:
> > >         > On 2006-01-06, Daniel Kunkel < DanielKunkel@BioWaves.com>
> > >         wrote:
> > >         > > Hi
> > >         > >
> > >         > > I'm trying to add another primary key to a table populated
> > >         with data and
> > >         > > a number of foreign key constraints.
> > >         >
> > >         > You can only have one primary key on a table.
> > >         >
> > >         > You can add additional unique constraints to get the same
> > >         effect. (A
> > >         > primary key constraint is just a unique constraint that is
> > >         also not null,
> > >         > and is the default target for REFERENCES constraints
> > >         referring to the table -
> > >         > this last factor is why there can be only one...)
> > >         >
> > >
> > >
> > >         ---------------------------(end of
> > >         broadcast)---------------------------
> > >         TIP 9: In versions below 8.0, the planner will ignore your
> > >         desire to
> > >                choose an index scan if your joining column's datatypes
> > >         do not
> > >                match
> > >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
--
Daniel

*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
Have a GREAT Day!

Daniel Kunkel           DanielKunkel@BioWaves.com
BioWaves, LLC           http://www.BioWaves.com
14150 NE 20th St. Suite F1
Bellevue, WA 98007
800-734-3588    425-895-0050
http://www.WizCity.com  http://www.Illusion-Optical.com
*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-