It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Definging a Unique Constraint Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Definging a Unique Constraint" Watch "Definging a Unique Constraint" New topic

Definging a Unique Constraint

Tim Jones

Joined: Apr 06, 2008
Posts: 28

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

Joined: May 26, 2003
Posts: 33102

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Definging a Unique Constraint
It's not a secret anymore!