Duplicate primary key validation

Question

I am building a java project. I want to check if a primary key already exist in my table. For example I have the below code:

private void AddProductActionPerformed(java.awt.event.ActionEvent evt) 
{                                           
    String query="INSERT INTO Products(Pro_Id ,Pro_Name,Pro_Price,Pro_Quantity,Pro_Supplier_id)VALUES ('"+Pro_Id.getText()+" ','"+Pro_Name.getText()+" ','"+Pro_Price.getText()+" ','"+Pro_Quantity.getText()+" ','"+Pro_Supplier_id.getText()+" ') ";
    executeSQLQuery(query,"Inserted");
}

How can I get a message that tells me to change the entry of primary key if it already exists?

Possible duplicate of [MySQL: Insert record if not exists in table](http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table) — DimaSan, Aug 29 '16 at 11:09
Catch the exception and deal with it. That is much more efficient then doing a `count()` before the insert - especially if you only expect very few errors. — a_horse_with_no_name, Aug 29 '16 at 11:47

score 1 · Accepted Answer · answered Aug 29 '16 at 11:36

You can put your code inside try catch block. Inside catch block check for SQLException

public static final int MYSQL_DUPLICATE_PK = 1062; // Replace 1062 with exception no. you are getting in case it is different for different    database

try{
    String query="INSERT INTO Products(Pro_Id  ,Pro_Name,Pro_Price,Pro_Quantity,Pro_Supplier_id)VALUES ('"+Pro_Id.getText()+" ','"+Pro_Name.getText()+" ','"+Pro_Price.getText()+" ','"+Pro_Quantity.getText()+" ','"+Pro_Supplier_id.getText()+" ') ";
    executeSQLQuery(query,"Inserted");
} catch(SQLException e){
if(e.getErrorCode() == MYSQL_DUPLICATE_PK ){
   System.out.println("Primary key already used");
}
}

i use this solution but he brings me one error he tells me that SQLException is never thrown in body of corresponding try statement incompatibly types ... — George Kontonikolaou, Aug 29 '16 at 12:04

score 0 · Answer 2 · answered Aug 29 '16 at 11:09

0

How can I get a message that tells me to change the entry of primary key if it already exists?

Make sure you have marked Pro_Id as PRIMARY KEY while defining your table structure which will make sure this behavior and if you try to insert duplicate value it will throw error.

answered Aug 29 '16 at 11:09

Rahul

71,392
13
57
105

On table structure is set as primary key . But when i add new row in my jtable i want to pop up a message < this pro_id alredy exist> if already exist .. any idea ? ty ! – George Kontonikolaou Aug 29 '16 at 11:18
@GeorgeKontonikolaou, I don't know Java but that would be easy ... use a try .. catch block ... in catch block show a message box with your message – Rahul Aug 29 '16 at 11:37

score 0 · Answer 3 · edited May 23 '17 at 11:51

You would get an error if you try your code and the key already exists. Depending on this error for your program to work during a normal flow is not a good idea, as exceptions are always expensive in terms of performance. What you should do is check if the primary key exists already before trying to insert. This can be done by executing a SELECT query.

SELECT 1 FROM Products WHERE Pro_Id = :yourDesiredPk;

When the result of the query is not empty it would mean that it already exists.

A better idea is to consider using a sequence and using the next value aka auto increment, check it out on google (What is a sequence (Database)? When would we need it?). That way you can avoid having duplicate PK problems. But maybe your PK is not a number and has some business logic behind it, in that case a sequence is not an option.

score 0 · Answer 4 · answered Aug 29 '16 at 11:52

0

Before insert record do one thing do count(*) and if count is 0 then and then insert the same otherwise show popup for duplicate query.

answered Aug 29 '16 at 11:52

Sejal Rudani

172
1
17

Duplicate primary key validation

4 Answers4