Thread: Adding another primary key to a populated table
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. Any suggestions? Would it work to dump the database, edit the sql, and re-import? Thanks in advance. -- 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, 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 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...) >
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
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
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? 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 > >
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