Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How do I prevent duplicate records inserted in the database

 
Kaiser Rahman
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I enter same record from the UI with name, address, Phone number, the record gets added in the database without complaints. So, it creates duplicate record in the database.

How do I programmatically prevent that to happen?
 
Kaiser Rahman
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know Trigger is a way from the database. But, that wouldn't work since the ID is generated dynamically for each record which is atleast different.

 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This post has been moved to the JDBC forum as it has nothing at all to do with JSP.

(You're not wirting Java code in a JSP, are you? )
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kaiser Rahman wrote:How do I programmatically prevent that to happen?


Your code should act like this:

"Is that record already in the database? Yes -- don't add it again. No -- add it to the database."

You might also want some code which notifies the user that the record was a duplicate, and hence not added to the database again.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Create a UNIQUE constraint on your database table for the relevant columns - name, address, phone number. This means the database will stop you creating duplicate records.

If you try to create a duplicate record, the SQL insert will fail and you will get an exception. You can catch this exception in your code and decide how to handle it.

Don't bother trying to check for duplicates just before you do the insert, because that is not reliable: somebody could insert a record with the same key values just after your check but just before your insert.

Only the database knows for sure if the record already exists, so let the database perform the check via the unique key constraint. That's exactly what it is designed for. It is also efficient, because the constraint is enforced via an index, so the check can be performed against the index instead of reading the whole table.
 
Claudiu Chelemen
Ranch Hand
Posts: 75
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would go for both Paul's and Chris's approaches:
- define what makes your record unique and create an unique constraint on those columns in the database (maintains data integrity)
- application wise, I think it's nice to check first that the record is not in the database, and if it's not, then INSERT it.

I would choose this approach as it allows me to output a relevant message to the user, otherwise I'd have to parse the SqlException to check what the problem was.
Should it be the unlikely case that another used adds the record between the select and the insert, you would end up with an SqlException and you could output a more generic error message.

Claudiu
 
Dave Tolls
Ranch Hand
Posts: 2099
15
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Claudiu Chelemen wrote:
I would choose this approach as it allows me to output a relevant message to the user, otherwise I'd have to parse the SqlException to check what the problem was.
Should it be the unlikely case that another used adds the record between the select and the insert, you would end up with an SqlException and you could output a more generic error message.

Claudiu


You still need to handle the exception, though, so why bother with the extra round trip to the database?
At the point of the exception you have just as much data for creating a meaningful error message.
 
Kaiser Rahman
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How about adding a UNIQUE constraint in the database?
 
Ron McLeod
Bartender
Pie
Posts: 1039
65
Android Eclipse IDE Java Linux MySQL Database Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should already have something similar in your DDL to the statements shown in black. To add a unique contraint, you would add something similar to the statements shown in blue.

CREATE TABLE friends (
   _id INTEGER PRIMARY KEY AUTOINCREMENT,
   name VARCHAR (80) NOT NULL,
   address VARCHAR (160) NOT NULL,
   phone VARCHAR (20) NOT NULL,

   UNIQUE (
      name,
      address,
      phone
   )

);

The exact syntax will vary based on the database - the link that Chris provided has examples for most common databases.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic