Best Practice to Check for Duplicates


Author
Message
Greg McGuffey
Greg McGuffey
Strategic Support Team Member (3.5K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
I'm wondering what the best practice is (or the trade offs are) for handling duplicates in a unique index. My pks are 99.9% auto increment integers, so that is never a problem, but I often have a unique index on one or two other fields or combination of fields. What is the preferred method to handle possible duplicates? I have thought of two methods:



- catch the sql exception and notify the user

- check the db first to see if the data would cause a duplicate, using BO methods.



What are you all doing? Why?



Thanks!
StrataFrame Team
S
StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)
Group: StrataFrame Developers
Posts: 3K, Visits: 2.5K
We generally query the database with a quick little ExecuteScalar to retrieve the count of records that have the same value.  We don't generally let the exception be thrown because we're always leery letting an exception totally blow away the completion of the call stack, so we try to inform the user of the problem before they get the exception.  Also, you can do the test in the CheckRulesOnCurrentRow so that you can inform the user through a broken rule and tell them they need to change the value.
StrataFrame Team
S
StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)StrataFrame Developer (4.6K reputation)
Group: StrataFrame Developers
Posts: 3K, Visits: 2.5K
However, in the off chance that some other user inserts the exact same value in between the business rules being checked and the INSERT into the database, don't forego the use of the unique key on the database (don't just rely on the business rule).  When this happens (window of some <.25 seconds or so depending upon network latency), the exception will be thrown and preserve your database consistency and the user can just try again...
Greg McGuffey
Greg McGuffey
Strategic Support Team Member (3.5K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
Thanks Ben!
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search