Adding another primary key to a populated table

On Thu, Jan 05, 2006 at 08:08:39PM -0800, Daniel Kunkel wrote:

Hi

I'm trying to add another primary key to a table populated with data and
a number of foreign key constraints.

The new column is populated with non-null data (a requirement as I
understand it to become a valid primary key)

I tried:

ALTER TABLE product_price
ADD CONSTRAINT product_price_pkey6 PRIMARY KEY
(product_id,product_price_type_id,currency_uom_id,product_store_group_id,from_date,product_price_purpose_id);

without having any luck.

What's the exact error you got?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

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

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

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

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?

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

On Thu, 5 Jan 2006, Daniel Kunkel wrote:

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.

What version are you using? The above sort of thing seems to work for me
on my 8.2 devel machine (in a small test, including recreating the key)
and I had thought it should on 8.1 as well.

Hi

Thank you for taking time to give that a try.

I'm currently running 7.3.10.

On Fri, 2006-01-06 at 07:50 -0800, Stephan Szabo wrote:

On Thu, 5 Jan 2006, Daniel Kunkel wrote:

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.

What version are you using? The above sort of thing seems to work for me
on my 8.2 devel machine (in a small test, including recreating the key)
and I had thought it should on 8.1 as well.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

On 1/6/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.

yes, but you have to drop all the dependant FOREIGN KEY constraints too

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Fri, 6 Jan 2006, Daniel Kunkel wrote:

Thank you for taking time to give that a try.

I'm currently running 7.3.10.

In that case, if you do a drop constraint and then try manually dropping
the index (probably drop index product_price_pkey), does it work?

Adding -general back in...

As someone else already mentioned, you can't have multiple primary keys on one table.

From: Daniel Kunkel [mailto:DanielKunkel@BioWaves.com]
ERROR: ALTER TABLE / PRIMARY KEY multiple primary keys for table
'product_price' are not allowed

On Thu, 2006-01-05 at 22:51 -0600, Jim C. Nasby wrote:

On Thu, Jan 05, 2006 at 08:08:39PM -0800, Daniel Kunkel wrote:

Hi

I'm trying to add another primary key to a table

populated with data and

a number of foreign key constraints.

The new column is populated with non-null data (a requirement as I
understand it to become a valid primary key)

I tried:

ALTER TABLE product_price
ADD CONSTRAINT product_price_pkey6 PRIMARY KEY

(product_id,product_price_type_id,currency_uom_id,product_stor
e_group_id,from_date,product_price_purpose_id);

without having any luck.

What's the exact error you got?

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461