How can I force a column to be unique for an entire table in SQL Server 2008 R2?

Question

I have a table with a Description field. I want to ensure that no two rows have the same "Description," but I can't make Description into my identity column (my ID column is an int).

Is it safe to set Description as a second primary key (in addition to my ID which is already a primary key)?

Answer 1

There is no such thing as a 'secondary primary key'. There is one primary key per table.

Create a UNIQUE constraint on the Description column (highly unusual thing to do, BTW. For example, It is more usual to create a unique index on Product Name rather than a Product description) or if you have null values in the Description column create a Filtered index (SQL Server 2008 onwards)

ALTER TABLE dbo.yourTable    ADD CONSTRAINT UQ_yourTable_Description UNIQUE ([Description]); 

Answer 2

Add a Unique index to the Description column.

Using Sql Server Management Studio right click on the table and choose Design. Then right click on a column and choose "Indexes/keys". You will be prompted with the following window

Click on Add on the bottom left and then specify properties for your index. If you want to use a DDL script then use something like this

CREATE UNIQUE NONCLUSTERED INDEX [IX_INDEXNAME] ON [dbo].[TABLENAME]  (     [Description] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] GO