473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Using sql server unique constraints

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...


Nov 16 '05 #1
4 2474
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.

--
venlig hilsen / with regards
anders borum
--
Nov 16 '05 #2

"Mark Halliday" <ma**@teleno.co.uk> wrote in message
news:ud**************@tk2msftngp13.phx.gbl...
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.

--
Peter [MVP Visual Developer]
Jack of all trades, master of none.
Nov 16 '05 #3
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)

"Mark Halliday" <ma**@teleno.co.uk> wrote in message
news:ud**************@tk2msftngp13.phx.gbl...
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...

Nov 16 '05 #4
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.

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Thu, 4 Nov 2004 13:15:20 -0000, "Mark Halliday" <ma**@teleno.co.uk>
wrote:
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...


Nov 16 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Rathtap | last post by:
I want to use the Identity field (increment 1,1) as a primary key and have a unique constraint on my other field which is of type char. I am worried that related data in other tables may lose...
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
5
by: pete | last post by:
Today I need to copy 8 records in a table. I have to use Access 200 because of the limitation of Enterprise Manager's inability to cope with field with more than 900 characters. Selected records,...
3
by: Bob | last post by:
Why, in the process of creating a unique index, does SQL Server allow me to select the "Ignore duplicate keys" option? Wouldn't I just create a non-unique index if I wanted to ignore duplicate...
1
by: Manu | last post by:
Hello Specialists ! Please help me - i need advice in importing textual data to SQL Server. I am using DTS with a simple process : Text(source)->Connection. I want to increase speed of...
6
by: Brian Basquille | last post by:
Just started learning SQL recently. But one thing i'm still not clear on is about altering relationships between tables after they've been created. Instead of creating a foreign key when the...
5
by: aj | last post by:
DB2 WSE 8.1 FP5 Red Hat AS 2.1 What is the difference between adding a unique constraint like: ALTER TABLE <SCHEMA>.<TABLE> ADD CONSTRAINT CC1131378283225 UNIQUE ( <COL1>) ; and adding a...
5
by: Monty M. | last post by:
Hello; I was wondering if anyone can assist me with this problem. Here are the tools I am using: Language: C# Database: MS SQL Server 2000 Application: Visual Studio 2005 1. I have a...
10
by: Phil Latio | last post by:
I am inserting data into user table which contains 5 fields, sounds simple enough normally but 2 of the fields are designated as UNIQUE. If someone does enter a value which already exists, how do I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.