Creating Unique Constraints

Unique constraint on a column to prevent duplicate entry on a column.

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

Using TSQL

-- Checks to see if index exist.
IF EXISTS(SELECT * FROM sysindexes WHERE id = OBJECT_ID('tblName01') AND name='IX_tblName01')
	DROP INDEX IX_tblName01 ON tblName01

-- Create Unique Index
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblName01] ON [dbo].[tblName01]
(
	[col01] 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