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
