Primary key violation

[Subrat Das]

Hi,

I have a table having composite primary key - 2 numeric columns and 1 varchar column . In my application, whenever i insert a row into a table,i check if the row exists in the table or not for the given primary key combination. If it exists i UPDATE the row, else insert.

But even after such checks, i am getting primary key vioaltion errors in bulk.

May be its due to the multithreaded java application which calls my stored proc.

But is there any chance that the varchar column in the composite primary key, is causing any problem.

Any ideas would be helpful.

Thanks a lot.

Subrat

[amitpatel66]

Hi,

I have a table having composite primary key - 2 numeric columns and 1 varchar column . In my application, whenever i insert a row into a table,i check if the row exists in the table or not for the given primary key combination. If it exists i UPDATE the row, else insert.

But even after such checks, i am getting primary key vioaltion errors in bulk.

May be its due to the multithreaded java application which calls my stored proc.

But is there any chance that the varchar column in the composite primary key, is causing any problem.

Any ideas would be helpful.

Thanks a lot.

Subrat

No VARCHAR column will not create any problem here!!
its the data which you are trying to update/insert is creating the problem!!

[Subrat Das]

My code goes like this:

CREATE PROCEDURE ins_Fab_topc_re qs_stats
(@Subj_id numeric(13,0),
@Info_upd_id numeric (13,0),
@Info_src_cd code ,
@Sys_id varchar (15),
@Tot_topc_cnt int,
@Prcs_topc_cnt int ,
@Prcs_topc_lst varchar (500) ,
@Faild_topc_lst varchar (500),
@Err_no int OUTPUT ,
@Err_msg varchar(1000) OUTPUT
)
AS
BEGIN
/*************** *************** *************** **********
Declaration of local variables
*************** *************** *************** ***********/
DECLARE @Err int
DECLARE @Err_Desc varchar(1000)
DECLARE @result int
DECLARE @TEMP_ERR INT


/*************** *************** *************** **********
Assigning default value for some local variables
*************** *************** *************** **********/
SELECT @Err = 0
SELECT @Err_Desc = NULL
SELECT @TEMP_ERR = 0

set nocount on

IF(SELECT COUNT(*)FROM Fab_topc_reqs_s tats_grate WHERE SUBJ_ID = @Subj_id AND Info_upd_id = @Info_upd_id AND SYS_ID = @Sys_id)= 0

BEGIN

BEGIN TRAN


IF (SELECT COUNT(*) FROM Fab_topc_reqs_s tats_grate WHERE SUBJ_ID = @Subj_id
AND Info_upd_id = @Info_upd_id AND SYS_ID = @Sys_id) = 0

BEGIN --insert begin

INSERT INTO Fab_topc_reqs_s tats_grate
(Subj_id,
Info_upd_id,
Info_src_cd,
Sys_id,
Tot_topc_cnt,
Prcs_topc_cnt,
Prcs_topc_lst,
Faild_topc_lst
)
SELECT
@Subj_id,
@Info_upd_id,
@Info_src_cd,
@Sys_id,
@Tot_topc_cnt,
@Prcs_topc_cnt,
@Prcs_topc_lst,
@Faild_topc_lst
WHERE NOT EXISTS
(SELECT 1 FROM Fab_topc_reqs_s tats_grate WHERE SUBJ_ID = @Subj_id AND Info_upd_id = @Info_upd_id AND SYS_ID = @Sys_id)
/***ERROR HANDLING *****/
SELECT @TEMP_ERR = @@ERROR
END --INSERT BEGIN ENDS HERE

IF @TEMP_ERR = 0
BEGIN
COMMIT TRAN
SELECT @Err = 0
SELECT @Err_Desc = 'INSERTION SUCESSFUL FOR THE SUBJ_ID : '+CONVERT(VARCH AR(50),@Subj_id )
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @Err = @TEMP_ERR
SELECT @Err_Desc = 'INSERTION FAILED FOR THE SUBJ_ID : '+CONVERT(VARCH AR(50),@Subj_id )
END



END
ELSE
BEGIN
BEGIN TRAN
UPDATE Fab_topc_reqs_s tats_grate
SET Info_src_cd = @Info_src_cd,
--Sys_id = @Sys_id,
Tot_topc_cnt = @Tot_topc_cnt,
Prcs_topc_cnt = @Prcs_topc_cnt,
Prcs_topc_lst = @Prcs_topc_lst,
Faild_topc_lst = @Faild_topc_lst ,
log_tmst = getdate()
WHERE SUBJ_ID = @Subj_id
AND Info_upd_id = @Info_upd_id
And SYS_ID = @sys_id


/***ERROR HANDLING *****/
IF @@ERROR = 0
BEGIN
COMMIT TRAN
SELECT @Err = 0
SELECT @Err_Desc = 'UPDATION SUCESSFUL FOR THE SUBJ_ID : '+CONVERT(VARCH AR(50),@Subj_id )
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @Err = @@ERROR
SELECT @Err_Desc = 'UPDATION FAILED FOR THE SUBJ_ID : '+CONVERT(VARCH AR(50),@Subj_id )
END
END

SELECT @Err_no=@Err
SELECT @Err_msg=@Err_d esc

END


Can any one see any flaw in it?

Regards,
Subrat