Re: update record with two-column primary key

Scott Marlowe wrote:
Charles Mortell 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?

Really it's a mistake to update a primary key.  A primary key by definition
identifies the row; changing it means to delete the row and insert a new one.

Updates are for when you've identified a specific row, that is, a specific
primary key.  That means that updates are only for dependent columns.

What you want to do is literally remove the old record and insert a new one.

The duplicate key violation is the purpose of having a primary key, after all.
  Otherwise you'd create two rows where you should only have one.

--
Lew