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
