2

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!

Tom Tucker
  • 11,676
  • 22
  • 89
  • 130
  • i may well be very wrong but I thought hibernate's entity manager take care of this for you? – Ramy Dec 20 '10 at 19:41
  • Hibernate will wrap up any error from the DBMS in an exception. I think Hibernate belongs in the database/persistence layer rather than the application layer. – Tom Tucker Dec 20 '10 at 19:48
  • Another advantage of 2nd over 1st is that 1st solution must perform check and insert in a atomic fashion to remain correct, whereas 2nd not. – Victor Sorokin Dec 22 '10 at 15:35

4 Answers4

3

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:

Community
  • 1
  • 1
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
2

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • 2
    There are primary key constraints and unique key constraints. I think it's fine to call them primary keys and unique keys without being redundant. – GriffeyDog Dec 20 '10 at 20:35
  • Primary keys are no different from keys that aren't "primary". Since primary keys are unique as well it would be pretty confusing (as well as redundant) to call other keys "unique" but to stop calling them "unique" if they are also "primary" keys. Don't confuse keys with SQL keywords - they are not the same thing. – nvogel Dec 21 '10 at 00:05
1

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.

AlexR
  • 114,158
  • 16
  • 130
  • 208
1

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.

Oswald
  • 31,254
  • 3
  • 43
  • 68
  • The database does the check automatically for you. So the question is does it really make sense to duplicate that check in the business layer? – nvogel Dec 20 '10 at 20:16