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

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
>
--
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
*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-