Unique Key Constraint

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 开发者_如何学JAVAviolation 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:

• Java - MySQL integrity violation handling

---

A third option is to use a MERGE operation (sometimes called UPSERT) if your DBMS supports it. There is usually a DBMS specific way of checking whether the row was inserted or not.

Avoid the tautology "unique" key. Keys ARE unique, so the word "key" is quite sufficient to say what you mean.

---

I like the "optimistic" approach ("do nothing"). You already enumerated the pros. You are right that in this case you delegate validation to the DB layer. But inf you are using JPA the DB layer is also generated by java layer, so actually your validation depends on your annotation in java code. Therefore it is not a big crime.

---

A unique key is usually a business requirement, so you should use the business layer to check whether the one you intend to use is available. Delegating the checking to the database is an optimization, that should only be done when needed.