Prevent duplicate data entry

[Ed Carrington]

I would like to make sure there are no duplicate data entries in my JSP that populates an Oracle database with a table called MainTable. MainTable has an Id field that is the primary key, ValData with a varchar data type, Fid and Fid2 are number data types. Fid and Fid2 are foreign key values that are taken from another table.

Id   ValData   Fid   Fid2
1    abc         34    2
2    efg          23    34
3    zeo         25    43

Sometimes someone can enter duplicate data and the ValData, Fid and Fid2 will end up like this:

Id   ValData   Fid   Fid2
1    abc          34    2
2    efg           23    34
3    zeo          25    43
4    zeo          25    43

Is there anything in Java I can implement to prevent duplicate data entry in the above example?

[Jeanne Boyarsky]

Ed,
Does it have to be in Java? The best thing to do is put in place a database constraint.

[Paul Campbell]

Originally posted by Ed Carrington:
I would like to make sure there are no duplicate data entries in my JSP that populates an Oracle database with a table called MainTable. MainTable has an Id field that is the primary key, ValData with a varchar data type, Fid and Fid2 are number data types. Fid and Fid2 are foreign key values that are taken from another table.

Id   ValData   Fid   Fid2
1    abc         34    2
2    efg          23    34
3    zeo         25    43

Sometimes someone can enter duplicate data and the ValData, Fid and Fid2 will end up like this:

Id   ValData   Fid   Fid2
1    abc          34    2
2    efg           23    34
3    zeo          25    43
4    zeo          25    43

Is there anything in Java I can implement to prevent duplicate data entry in the above example?

I'm assumming ID is a database sequence that is auto-incremented... in your app you should try to do something likey this (sorry this is the PL/SQL version... but I'm a long way from Java/JDBC profecient):
BEGIN
Select 1
FROM MainTable
WHERE ValData = myValData
AND Fid = myFid
AND Fid2 = myFid2

Exception
When no_data_found Then
INSERT INTO MainTable (ValData, Fid, Fid2)
VALUES (myValData, myFid, MyFid2);
END


My idea differs than Jeanne's because I come from a database-centric background. This doesn't make my suggestion better... it's just a suggestion. Without knowing more about how the data in the table was used... I would just handle it outside of adding more RI to my database.
[ October 21, 2007: Message edited by: Paul Campbell ]

[Jeanne Boyarsky]

Paul: The funny thing is that I suggested a DB solution and you suggested a logical one.

Ed: If you translate Paul's solution to Java, make sure you place both JDBC calls inside a transaction. Otherwise, the database state could change after the select.

Both solutions are valid and mainly a matter of preference. I lean towards the constraint (RI = referential integrity) because in my apps it isn't guaranteed that nobody else will be updating the values. And even if it was, someone could be helpful and update the database by hand. I prefer the database enforce something if it is important to me.

[Ed Carrington]

Thanks it now works great.

I ended up creating constraints in the Oracle Database:

alter table maintable
            add constraint maintable_myNamehere unique (valdata, fid,fid2);

In the Helper Class that is called by my Servlet Controller I put in a condition (ORA-00001 is Oracle's Duplicate message) in the SQLException Catch area and it catches all my Duplicate entry attempts and shows message to the user.

Here is what I have and would like to know if this is the way you would do it??

try {
   // db stuff
} catch (SQLException sqle) {
   String message = sqle.getMessage();
   if (message.indexOf("ORA-00001") != -1) {
      out.println("Duplicate Data entry.");
   } else {
       out.println("some other ORA error");
   }
}

[ October 22, 2007: Message edited by: Ed Carrington ]

[Jeanne Boyarsky]

Ed,
That's what I would do. The only thing I would change is to make the "ORA-00001" a constant. I wouldn't want to have to keep looking up what it means!

[Paul Campbell]

Originally posted by Jeanne Boyarsky:
Paul: The funny thing is that I suggested a DB solution and you suggested a logical one.

I found it amusing too.