473,320 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Primary key violation

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
Dec 24 '07 #1
2 1727
amitpatel66
2,367 Expert 2GB
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!!
Dec 24 '07 #2
My code goes like this:

CREATE PROCEDURE ins_Fab_topc_reqs_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_stats_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_stats_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_stats_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_stats_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(VARCHAR(50),@Subj_id)
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @Err = @TEMP_ERR
SELECT @Err_Desc = 'INSERTION FAILED FOR THE SUBJ_ID : '+CONVERT(VARCHAR(50),@Subj_id)
END



END
ELSE
BEGIN
BEGIN TRAN
UPDATE Fab_topc_reqs_stats_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(VARCHAR(50),@Subj_id)
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @Err = @@ERROR
SELECT @Err_Desc = 'UPDATION FAILED FOR THE SUBJ_ID : '+CONVERT(VARCHAR(50),@Subj_id)
END
END

SELECT @Err_no=@Err
SELECT @Err_msg=@Err_desc

END


Can any one see any flaw in it?

Regards,
Subrat
Dec 24 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: J Smith | last post by:
After doing some googling through the lists and such, I wasn't able to arrive at a solution for a problem I've run into recently. I've seen mention of similar symptoms, but my case seems different....
14
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought...
18
by: Thomas A. Anderson | last post by:
I am a bit confused in creating a composite primary key. I have three table with two of the tables containing primary keys. I have two of the tables (each with a primary key) having one to many...
2
by: John | last post by:
The ASP application inserts transaction records in transaction table with the system time as the primary key. However, it is possible to have primary key violation because the records in...
2
by: mivey4 | last post by:
Okay I have 2 tables: Table A - holds a list of new hardware serial numbers and their corresponding model (no constraints or indexes) Table B - holds a distinct list of current serial numbers...
2
by: Hetal | last post by:
I searched online and went through the forums as well, but i could not find a way to capture the database primary key violation exception. Any help will be much appreciated. Thanks, Hetal
2
by: embarkr | last post by:
I am getting the error: "Violation of PRIMARY KEY constraint 'PK_tblCustomsTariffTreeMap'. Cannot insert duplicate key in object 'dbo.tblCustomsTariffCodeTreeMap'." However, the record I am...
1
by: Zamdrist | last post by:
Violation of PRIMARY KEY constraint 'PK_CUSTOM2'. Cannot insert duplicate key in object 'MHGROUP.Custom2' Is there ANY other reason this violation of the primary key would happen OTHER than a...
7
by: akmaRudiliyn | last post by:
Hai everybody :). I have problem and need help. ERROR: ERROR Violation of PRIMARY KEY constraint 'PK_Table1_01'. Cannot insert duplicate key in object 'dbo.table1'. ERROR The statement has...
2
by: Se0ng | last post by:
i using vb.net and and insert into sql client, there is 3 column in a table, which r id, password and level, n just an empty table, when i do this Imports System.Data.SqlClient Public...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.