What's the best practice in handling data insertion/update on a table with unique key constraints at the application level? These are what I came up with:
1. Run a query on the table before inserting data to see if it will violate the constraint.
Pros
- You have full control so you don't have to deal with any DBMS specific error messages.
- Addtional layer of data integrity check
Cons
- There might be a performance hit since there will be no constraint violation most of the time.
- You will need to lock the table while you run the query for duplicate data.
2. Do nothing. Update the table and see what sticks.
Pros
- Simple!
- Faster overall since you don't have to run an additional query everytime you update a table.
Cons
- Your validation routine depends on the database layer.
- If the data doesn't stick, you have to wade through the stack trace to find the cause.
Which one is the more widely accepted solution? Are there alternatives to these?
I'm using Java, JPA, Hibernate, and Spring BTW. Any suggestions, even framework specific, are welcome!
Thanks!
You've already pretty much sum it up. If performance is a concern, go for 2nd way. If integrity is a concern, go for 1st way.
I personally favor integrity over performance. Hardware is pretty cheap, integrity not.
Related questions: