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
- In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.
- On the Table Designer menu, click Indexes/Keys.
- In the Indexes/Keys dialog box, click Add.
- In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.
- On the File menu, click Save table name.
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