Violation of PRIMARY KEY constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Violation of PRIMARY KEY constraint

Author

Topic

rchiu5hk
Starting Member

7 Posts

Posted - 2008-11-12 : 05:04:44

I have a list of insert statement which prompt error in some of them.
please tell me why??
---------------error---------------------------------------------
Server: Msg 2627, Level 14, State 1, Line 365
Violation of PRIMARY KEY constraint 'pk_contract_dtl_price'. Cannot insert duplicate key in object 'contract_dtl_price'.
The statement has been terminated
------------------'pk_contract_dtl_price------------------------
index_name
pk_contract_dtl_price

index_description
clustered, unique, primary key located on PRIMARY

index_keys
company_code, contract_no, item_code, start_date
-----------------insert statement------------------------------
insert into contract_dtl_price values ( @s_company_code, @s_contract_no, @s_item_code,'2008/11/01', @d_end_date, @n_new_price, 0, @d_last_docket_ref, 'Raymond',
getdate(), 'SYSTEM' ,'SYSTEM' , getdate(), 'SYSTEM' )
--------------no record duplicated----------------------
Company_code Contract_no item_code Start_date End_date
ABC CASH 1 2004-11-1 2007-12-15
ABC CASH 1 2007-12-16 2008-4-9
ABC CASH 1 2008-4-10 2008-8-15
ABC CASH 1 2008-8-16 2009-12-31
----------------table structure -------------------------
Column_name Type Length Prec Scale
company_code char 3
contract_no char 12
item_code char 10
start_date datetime 8
end_date datetime 8
unit_price numeric 5 9 3
other_price numeric 5 9 3
last_docket_ref datetime 8
crt_by char 15
crt_dt datetime 8
crt_host char 15
upd_by char 15
upd_dt datetime 8
upd_host char 15

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 05:15:51

You are trying to insert duplicate values in the table.

E 12°55'05.63"
N 56°04'39.26"

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-12 : 05:26:57

How do you creating the PK in the table.

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 06:11:03

you need to ensure the combination of company_code, contract_no, item_code, start_date values which you're trying to insert is not aleardy existing on table. may bemodify code like below


if not exists(select 1 from contract_dtl_price where company_code=@s_company_code
and contract_no=@s_contract_no
and item_code =@s_item_code
and start_date='2008/11/01')
insert into contract_dtl_price values ( @s_company_code, @s_contract_no, @s_item_code,'2008/11/01', @d_end_date, @n_new_price, 0, @d_last_docket_ref, 'Raymond',
getdate(), 'SYSTEM' ,'SYSTEM' , getdate(), 'SYSTEM' )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 06:12:36

Or just have the primary key created with "IGNORE DUPLICATE VALUES"?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources