Using sql server unique constraints

[Mark Halliday]

I'm writing a windows app that uses sql server. Some of my entities have
properties that must be unique. I'm not sure whether I should be performing
manual checks on save or use the unique constraints in sql server, or even
do both....

For example I have a Company entity with a Code property that should be
unique.

In my Company.Save method I could run a select to check whether there are
any existing rows with this code and only update / insert if nothing's
returned. This leaves the theoretical possibility that another user could
insert a duplicate code in the split seconds after my select, but my before
update.

Or, I could add a unique constraint on the underlying column in SqlServer
and get Company.Save to catch and interpret the specific SqlException that
will be thrown if the constraint is broken.

The second option seems safer, but is this overkill? Does anyone else have
other suggestions? Any comments are welcome...

 

[Anders Borum]

Hello!

The second approach will ensure that once the first transaction completes
(possible from another thread), the second transaction will fail because of
the validation against the constraints.

 

[Peter van der Goes]

I'm writing a windows app that uses sql server. Some of my entities have
properties that must be unique. I'm not sure whether I should be performing
manual checks on save or use the unique constraints in sql server, or even
do both....

For example I have a Company entity with a Code property that should be
unique.

In my Company.Save method I could run a select to check whether there are
any existing rows with this code and only update / insert if nothing's
returned. This leaves the theoretical possibility that another user could
insert a duplicate code in the split seconds after my select, but my before
update.

Or, I could add a unique constraint on the underlying column in SqlServer
and get Company.Save to catch and interpret the specific SqlException that
will be thrown if the constraint is broken.

The second option seems safer, but is this overkill? Does anyone else have
other suggestions? Any comments are welcome...

I'd suggest both. The application should do the most it can to ensure that
invalid transactions are not sent to the database, and the database itself
should apply constraints to protect your data. If you end up with only one
layer of protection, the constraint is the way to go.

 

[Mark Broadbent]

Personally I would do this as a two phased op.
1. As unique properties are entered onto form you could do a validation
check against the sql server for the existance of that value there and then.
2. Once form values are submitted by the user, you should catch any sql
exceptions and handle them as per required

For 1. ideally those properties should be indexed otherwise you might have a
lot of table scans going on.
For 2. Using SQL's ability to maintain unique values is preferable to
performing this on the front end (otherwise this uniqueness could be broken
on the backend)

 

[Scott Allen]

In addition to the other comments I'll add that using a unique
constraint in SQL Server has some additional side benefits. The
constraint is implemented with an index, which can be a performance.
Telling MSSQL the column is unique is also a hint to the optimizer
which can build more efficient query plans if the column is involved
in any joins.