Re: Adding another primary key to a populated table - Mailing list pgsql-general

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