Hello everyone!
could you please tell me the resolution steps for -803(Duplicate key on insert or update.)?
SQL error code -803
- Robert Sample
- Global Moderator
- Posts: 1891
- Joined: Fri Jun 28, 2013 1:22 am
- Location: Dubuque Iowa
Re: SQL error code -803
What is the business logic for a duplicate key? Depending upon the application, you could ignore the duplicate record, update fields with data from the duplicate, replace the entire record, or change the key and write it (there can be other possibilities as well). Which of these applies depends upon your site, its applications, and what the business wants. As such, nobody -- on this forum or elsewhere -- can provide you a definitive answer; you MUST work with your site personnel to identify the proper solution and implement it.
-
- Global Moderator
- Posts: 588
- Joined: Wed Nov 20, 2013 11:53 am
- Location: Mars
Re: SQL error code -803
Sqlcode -803 occurs due to duplicate key during insert / Update .
It could be either due due duplicate data or due to bad programming/Design. So you as Robert says you need to understand what is causing the duplicate scenario and try to resolve it by cordinating with the stakeholders
It could be either due due duplicate data or due to bad programming/Design. So you as Robert says you need to understand what is causing the duplicate scenario and try to resolve it by cordinating with the stakeholders
zprogrammer
- Anuj Dhawan
- Founder
- Posts: 2801
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: SQL error code -803
As has been stated, SQLCODE -803 comes due to duplication of rows in a table which has a unique constraint defined on it. There are three condition when a DB2 program can issue SQLCODE -803:
Delete: Application program tries to delete an existing record in a table which cascades the delete, set to "Null", to a child table. While the child table has a constrain to have unique records. This can also cause duplicates the child table.
Insert: The application program tries to insert a duplicate record in a table which is constrained to have unique records.
Update: You/application programs are trying to update an existing record in a table (which has a constrained to have unique records)
that will cause the duplication of record in the table.
Below a pseudo code for reference, however it all depicts a way to handle the duplicate which essentially depends on your business need and logic:
Delete: Application program tries to delete an existing record in a table which cascades the delete, set to "Null", to a child table. While the child table has a constrain to have unique records. This can also cause duplicates the child table.
Insert: The application program tries to insert a duplicate record in a table which is constrained to have unique records.
Update: You/application programs are trying to update an existing record in a table (which has a constrained to have unique records)
that will cause the duplication of record in the table.
Below a pseudo code for reference, however it all depicts a way to handle the duplicate which essentially depends on your business need and logic:
Code: Select all
.
.
PROCESS-ADD-AUTH.
PERFORM MOVE-FIELDS-TO-DCLGEN
PERFORM SQL-FOR-ADD
EVALUATE TRUE
WHEN SQLCODE = 0 DISPLAY 'SUCCESSFUL ADD'
WHEN SQLCODE = -803
DISPLAY 'CANNOT ADD '
DISPLAY 'DUPLICATE -803'
WHEN SQLCODE > 0 OR SQLWARN0 = 'W'
PERFORM WARNING-PARA
WHEN SQLCODE < 0 GO TO ERROR-EXIT
END-EVALUATE.
PROCESS-CHANGE-AUTH.
PERFORM MOVE-FIELDS-TO-DCLGEN
PERFORM SQL-FOR-CHANGE
EVALUATE TRUE
WHEN SQLCODE = 0
DISPLAY 'SUCCESSFUL CHANGE'
WHEN SQLCODE = -803
DISPLAY 'CANNOT CHANGE'
DISPLAY 'DUPLICATE -803'
.
.
Thanks,
Anuj
Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Anuj
Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Create an account or sign in to join the discussion
You need to be a member in order to post a reply
Create an account
Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute