Re: update record with two-column primary key

On Nov 12, 2007 10:41 AM, Charles Mortell <cmortell(at)apt-cafm(dot)com> wrote:
> 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"

This is not a complete example. What are the two fields in your pkey?
I'm guessing business and list. If list is one, what are the values
for list in your table where business=13 and business=7. If you look
those up you should see one there with 13 for business and whatever
for list that is causing this problem.

> Is something wrong or is it just not possible to update a two-column primary
> key?

Good lord no. You're just making a simple mistake is all. Here, look:

create table test (a int, b int, c text, primary key (a,b))
insert into test values (1,2,'abc');
insert into test values (2,2,'abc');
-- Now I update a without checking on b...
update test set a=1 where a=2;
ERROR: duplicate key violates unique constraint "test_pkey"