update record with two-column primary key

From: "Charles Mortell" <cmortell(at)apt-cafm(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: update record with two-column primary key
Date: 2007-11-12 16:41:11
Message-ID: OBEBKAHJIDHDLCIIMKHKMEFJCIAA.cmortell@apt-cafm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Using PG 8.0 on Windows, I have a table 'business_list' with a two column
primary key. It's a linking table and it's primary keys are the keys from
the two tables I am linking: item_id and business.
Should I be able to update one of those primary key fields?

Here is the SQL:
UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and
business = 7;

It causes the following error: ERROR: duplicate key violates unique
constraint "data_business_list_pkey"

I need the 'business' value in the WHERE to locate the proper record to
update but it also seems to be preventing the update.

Is something wrong or is it just not possible to update a two-column primary
key? I didn't see any directly relevant info in the forum or documentation.
Should I add a surrogate key? That would be disappointing since the existing
2 column PK does satisfy 3rd Normal Form.
Thanks in advance for any suggestions!

Charles Mortell
Advanced Planning Technologies, Inc.
Mississippi River port of La Crosse, WI
cmortell at apt-cafm dot com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Sabino Mullane 2007-11-12 16:43:09 Re: plperl and regexps with accented characters - incompatible?
Previous Message Erwin Moller 2007-11-12 16:27:38 Strange error while working with derived table