Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Definging a Unique Constraint  RSS feed

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

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).


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"));
Jeanne Boyarsky
author & internet detective
Posts: 36406
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Boost this thread!