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

User · Answer

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

User · Answer

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- gt Click hover New Index- gt 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

User · Answer

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

User · Answer

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

[sql-server] Unique constraint on multiple columns

Examples related to sql-server

Examples related to sql-server-2008

Examples related to ssms