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(at)biowaves(dot)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(at)BioWaves(dot)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(at)BioWaves(dot)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
*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-