Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Definging a Unique Constraint

 
Tim Jones
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have the following fields "time_slot", "weekdays" and "room_number" in a table called "modules". I am trying to check for the following constraint before inserting a new record into the table. . If a record already exists with a "time_slot" on a specifc "weekday" within a specific "room_number" than do not save a new rcord (this is multiple of the record already in the db).

Example

If there exists a record in the table with time_slot = "9:00", Weekday="Monday", Room_Number="F123" ... then another record cannot exist under these constraints i.e a duplicate record cannot be created with these values time_slot = "9:00", Weekday="Monday", Room_Number="F123" ...

Here is the Insert code I guess I need a way to check if there already exists a record that has the above constraint before executing the following code. Can someone please show me an example of how to do this?

PreparedStatement new_module = connection.prepareStatement("INSERT INTO module (room_number, module_code, title, provider, duration, time_slot, weekdays)" + "VALUES(?,?,?,?,?,?,?)");
new_module.setString(1, request.getParameter("room_number"));
new_module.setString(2, request.getParameter("module_code"));
new_module.setString(3, request.getParameter("title"));
new_module.setString(4, request.getParameter("provider"));
new_module.setInt(5, 1);
new_module.setString(6, request.getParameter("time_slot"));
new_module.setString(7, request.getParameter("weekdays"));
new_module.executeUpdate();
connection.commit();
new_module.close();
response.sendRedirect("index.jsp");
connection.setAutoCommit(savedCommitValue);
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34672
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim,
There are two possible approaches:
1) Do a select before the insert to see if it already exists - with this approach you need a transaction so two inserts don't occur simultaneously.
2) Enable a database constraint and catch the exception as a duplicate.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic