SQL Server Tip : Preventing Duplicate Records Using the “Unique” Constraint

ParCon_HP_Headerbilder_Folgeseite_Dubletten_01Sometimes as a Software Developer or Database Administrator, you almost always have to manage a database table that must must be free from any duplications whatsoever.

So how do you solve the problem? There are two ways of managing such a table, either by building-in functionality into your application code (C#, VB.NET for example), resulting in several lines of code, or you could do it the easy way, which is simply letting SQL Server do all the heavy lifting for you. Lets focus on the SQL Server route, the easy way!

While it follows that every table in your database must have a Primary key constraint, often its an auto generated value useful when carrying out most database maintenance tasks. However the primary key itself will  not protect you from user generated or user captured data that may contain duplications. Enter the “Unique” constraint! This is a very powerful table-level constraint that you can apply to your table against a chosen table column, which can greatly assist to prevent duplicates in your data. For example, say you have a “Users” table and in it, you have an EmailAddress column, surely it would be strange to capture 1 or 2 users who have an identical email address. So how do we create a “Unique” constraint?

 

Using SQL Server Management Studio

  1. In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.
  2. On the Table Designer menu, click Indexes/Keys.
  3. In the Indexes/Keys dialog box, click Add.
  4. In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.
  5. On the File menu, click Save table name.

 

UniqeConstraint1UniqeConstraint2

 

Using Transact-SQL

Using Transact-SQL you could create the UNIQUE constraint at table creation time or you can alter an existing table. To apply the Unique constraint at table creation time you would structure your code as below:

 

USE TestDB;
GO
CREATE TABLE Users
 (
   UserID int NOT NULL  IDENTITY(1,1), 
   Name varchar(100) NOT NULL,
   Surname varchar(100) NOT NULL,
   EmailAddress varchar(100) NOT NULL,
   CONSTRAINT AK_UnqueEmail UNIQUE(EmailAddress) 
); 
GO

If you are applying the Unique constraint to an existing table you would structure your code as below:

USE TestDB; 
GO
ALTER TABLE Users 
ADD CONSTRAINT AK_UniqueEmail UNIQUE (EmailAddress); 
GO

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s