Unique constraint on multiple columns

CREATE TABLE [dbo].[user]( [userID] [int] IDENTITY(1,1) NOT NULL, [fcode] [int] NULL, [scode] [int] NULL, [dcode] [int] NULL, [name] [nvarchar](50) NULL, [address] [nvarchar](50) NULL, CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED ( [userID] ASC ) ) ON [PRIMARY] GO 

How do I add a unique constraint for columns fcode, scode, dcode with t-sql and/or management studio? fcode, scode, dcode must be unique together.

4 Answers

If the table is already created in the database, then you can add a unique constraint later on by using this SQL query:

ALTER TABLE dbo.User ADD CONSTRAINT ucCodes UNIQUE (fcode, scode, dcode) 

By using the constraint definition on table creation, you can specify one or multiple constraints that span multiple columns. The syntax, simplified from technet's documentation, is in the form of:

CONSTRAINT constraint_name UNIQUE [ CLUSTERED | NONCLUSTERED ] ( column [ ASC | DESC ] [ ,...n ] ) 

Therefore, the resuting table definition would be:

CREATE TABLE [dbo].[user]( [userID] [int] IDENTITY(1,1) NOT NULL, [fcode] [int] NULL, [scode] [int] NULL, [dcode] [int] NULL, [name] [nvarchar](50) NULL, [address] [nvarchar](50) NULL, CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED ( [userID] ASC ), CONSTRAINT [UQ_codes] UNIQUE NONCLUSTERED ( [fcode], [scode], [dcode] ) ) ON [PRIMARY] 

This can also be done in the GUI. Here's an example adding a multi-column unique constraint to an existing table.

1. Under the table, right click Indexes->Click/hover New Index->Click Non-Clustered Index...

2. A default Index name will be given but you may want to change it. Check the Unique checkbox and click Add... button

3. Check the columns you want included

Click OK in each window and you're done.

USE [TSQL2012] GO /****** Object: Table [dbo].[Table_1] Script Date: 11/22/2015 12:45:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Table_1]( [seq] [bigint] IDENTITY(1,1) NOT NULL, [ID] [int] NOT NULL, [name] [nvarchar](50) NULL, [cat] [nvarchar](50) NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Table_1] UNIQUE NONCLUSTERED ( [name] ASC, [cat] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO