how to avoid inserting duplicate key to the table?

J

jrefactors

how to avoid inserting duplicate key to the table?

For example, the EMPLOYEE TABLE has field EMPID (primary key) and NAME

In the Java program, if the following sql statement one by one, it will
throw SQLException.

INSERT INTO EMPLOYEE (EMPID, NAME) VALUES ('1001','Joe');
INSERT INTO EMPLOYEE (EMPID, NAME) VALUES ('1001','Sue');


In my case, should I check if the key value already exists in the
table, then update the record instead of inserting the record?

please advise. thanks!!
 
P

Pete Barrett

how to avoid inserting duplicate key to the table?

For example, the EMPLOYEE TABLE has field EMPID (primary key) and NAME

In the Java program, if the following sql statement one by one, it will
throw SQLException.

INSERT INTO EMPLOYEE (EMPID, NAME) VALUES ('1001','Joe');
INSERT INTO EMPLOYEE (EMPID, NAME) VALUES ('1001','Sue');


In my case, should I check if the key value already exists in the
table, then update the record instead of inserting the record?
Yes.

please advise. thanks!!

Alternatively, catch the SQLException and try to update the record in
the exception handler. That's rather less efficient if the
SQLException denotes a major problem (lost connection, for example)
instead of an attempt to insert a duplicate key. On the other hand,
it's a bit more efficient if most insertions will work, and only the
occasional one will be an attempt to insert a duplicate key.


Pete Barrett
 
E

E.Otter

With Oracle databases, there is a thing called a sequence which are often
used to get/create unique ID numbers because they never return the same
value twice. So... "SELECT mysequence.NEXTVAL FROM DUAL" would return an ID
number you know that will not be repeated by another instance of your
program. There are probably similar things in MS SQL Server and other DB's.

I would recommend you read the documentation of whatever database you are
connecting to and find out its equivalent to the "Sequence".
 
J

Joan

E.Otter said:
With Oracle databases, there is a thing called a sequence which are often
used to get/create unique ID numbers because they never return the same
value twice. So... "SELECT mysequence.NEXTVAL FROM DUAL" would return an ID
number you know that will not be repeated by another instance of your
program. There are probably similar things in MS SQL Server and other DB's.

I would recommend you read the documentation of whatever database you are
connecting to and find out its equivalent to the "Sequence".

This might be fine in some cases but the OP is trying to give the same
EMPLOYEE NUMBER to two employees which is a no-no as far as the IRS
is concerned.

BTW:
If you look hard at the question and the behaviour of the program
you will see that the OP doesn't have to do anything to "avoid....."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top