File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC 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
Bookmark "Definging a Unique Constraint" Watch "Definging a Unique Constraint" New topic
Author

Definging a Unique Constraint

Tim Jones
Greenhorn

Joined: Apr 06, 2008
Posts: 28
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

Joined: May 26, 2003
Posts: 31079
    
163

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Definging a Unique Constraint