Re: Adding another primary key to a populated table

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
> >