Thread: BUG #2511: violation of primary key on update with 2 tables

The following bug has been logged online:

Bug reference:      2511
Logged by:          james
Email address:      james@mercstudio.com
PostgreSQL version: 8.1.1
Operating system:   windows xp professional sp2
Description:        violation of primary key on update with 2 tables
Details:

hi,

i've tried to update cust_survey_answer table...
this table links to customer table on
cust_survey_answer.memberno = cust_survey_answer.memberno

cust_survey_answer primary key was
memberno & question_no

this table basically have unique combination of both
memberno & question_no ( answer sheet for customers )

right now, there are confirmed no duplicate of memberno & question_no
combination.

even in customer table, all memberno are uniques...
no duplicate memberno inside customer table.

what i did was, i combined the 2 table, and take the value
customer.dealercode to combine as 1 and store into
cust_survey_answer.memberno...

but when i run the query, the result shows me violation of primary key of
cust_survey_answer...


my temporary solution was, i remove the primary key of the
cust_survey_answer, and ran the script below:
================

update cust_survey_answer set memberno='0'+cast(customer.dealercode as
varchar
(5)) +'-'+ cust_survey_answer.memberno from customer where
cust_survey_answer.memberno=customer.memberno and customer.dealercode is not
null
and length( trim( customer.dealercode ) ) > 0 and
cust_survey_answer.memberno not like '%-%'
and cust_survey_answer.memberno is not null and cust_survey_answer.memberno
<> ''

=============

after i've runs it, i try set back the primary key for table
cust_survey_answer ( combination of memberno & question_no ) , and IT WORKS!


i was surprised..

i think it's a bug in postgresql ...
please help...

thank you.

best regards,

James

On Mon, 2006-07-03 at 11:14 +0000, james wrote:

> i think it's a bug in postgresql ...

Primary Key constraints are not deferrable currently. This is a known
situation so this isn't a bug.

I would advise you not to update the PK of your tables. That usually
represents a modelling problem and so I'm not sure if people will spend
time on trying to make that work.

Use an indirect key or some other mechanism, such as Inserting into
another table and renaming them.

--
  Simon Riggs
  EnterpriseDB          http://www.enterprisedb.com