Unique constraint on multiple columns

Question

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.

Does that mean that you can have many of the same fcode OR scode OR dcode but never two records with the same fcode AND scode AND dcode? — Jimbo, Aug 13 '10 at 06:13

marc_s · Answer 1 · 2015-10-26T09:42:47.553

479

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)

edited Oct 26 '15 at 09:42

answered Aug 13 '10 at 06:34

marc_s

732,580
175
1,330
1,459

1

Or `ADD CONSTRAINT ucCodes UNIQUE NONCLUSTERED` ? – Kiquenet Nov 22 '16 at 09:51
If all columns fcode, scode, dcode are NULL, not applies constarint ? If any column fcode, scode, dcode is NULL, not applies constarint ? – Kiquenet Apr 22 '21 at 10:24
1

@Kiquenet: not entirely sure what you're asking - `NULL` is handled like any other value, which means: if you have a unique constraint across those three columns, there can **only** be a single row with each of them being `NULL`. – marc_s Apr 22 '21 at 12:15
https://stackoverflow.com/questions/30443637/tsql-multi-column-unique-constraint-that-also-allows-multiple-nulls – Kiquenet Apr 22 '21 at 13:28

score 321 · Accepted Answer · edited Dec 29 '16 at 18:22

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]

Tony L. · Answer 3 · 2020-08-05T13:58:19.783

68

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

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

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

Check the columns you want included

Click OK in each window and you're done.

edited Aug 05 '20 at 13:58

answered Aug 18 '16 at 15:03

Tony L.

17,638
8
69
66

11

NOTE: This option is not available if you already have the table open in design view. So close the design tab first before doing it. – musefan Dec 04 '19 at 12:31

score 0 · Answer 4 · edited Nov 15 '17 at 16:51

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

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run. — Bono, Nov 22 '15 at 10:35

Unique constraint on multiple columns

4 Answers4

Linked

Related