• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How do I prevent duplicate records inserted in the database

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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? )
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 75
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Rancher
Posts: 4801
50
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How about adding a UNIQUE constraint in the database?
 
Marshal
Posts: 4501
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
and POOF! You're gone! But look, this tiny ad is still here:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic