Skip to content

Latest commit

 

History

History
123 lines (87 loc) · 5.16 KB

create-unique-constraints.md

File metadata and controls

123 lines (87 loc) · 5.16 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords monikerRange
Create unique constraints
This article shows you how to create unique constraints using SQL Server Management Studio and Transact-SQL.
WilliamDAssafMSFT
wiassaf
12/20/2023
sql
table-view-index
how-to
UNIQUE_TSQL
UNIQUE constraints [SQL Server], creating
constraints [SQL Server], creating
constraints [SQL Server], unique
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

Create unique constraints

[!INCLUDE sqlserver2016-asdb-asdbmi-fabricsqldb]

You can create a unique constraint in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql] to ensure no duplicate values are entered in specific columns that don't participate in a primary key. Creating a unique constraint automatically creates a corresponding unique index.

Note

For information on unique constraints in Azure Synapse Analytics, see Primary key, foreign key, and unique key in Azure Synapse Analytics.

Permissions

Requires ALTER permission on the table.

Use SQL Server Management Studio (SSMS)

Create a unique constraint using SSMS

  1. In Object Explorer, right-click the table to which you want to add a unique constraint, and select Design.

  2. On the Table Designer menu, select Indexes/Keys.

  3. In the Indexes/Keys dialog box, select Add.

  4. In the grid under General, select Type and choose Unique Key from the dropdown list box to the right of the property, and then select Close.

  5. On the File menu, select Save table name.

Use Transact-SQL

Create a unique constraint using Transact-SQL

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. The example creates the table TransactionHistoryArchive4 and creates a unique constraint on the column TransactionID.

    USE AdventureWorks2022;  
GO  
CREATE TABLE Production.TransactionHistoryArchive4  
 (  
   TransactionID int NOT NULL,   
   CONSTRAINT AK_TransactionID UNIQUE(TransactionID)   
);   
GO

Create a unique constraint on an existing table

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. The example creates a unique constraint on the columns PasswordHash and PasswordSalt in the table Person.Password.

    USE AdventureWorks2022;   
GO  
ALTER TABLE Person.Password   
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);   
GO

Create a unique constraint on a new table

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. The example creates a table and defines a unique constraint on the column TransactionID.

    USE AdventureWorks2022;  
GO  
CREATE TABLE Production.TransactionHistoryArchive2  
(  
   TransactionID int NOT NULL,  
   CONSTRAINT AK_TransactionID UNIQUE(TransactionID)  
);  
GO

Create a unique constraint on a nullable column

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. The example creates a filtered unique constraint using the CREATE UNIQUE INDEX syntax, only enforcing uniqueness on non-NULL values.

    USE AdventureWorks2022;  
GO
CREATE UNIQUE INDEX UQ_AdventureWorksDWBuildVersion
ON dbo.AdventureWorksDWBuildVersion (DBVersion)
WHERE (DBVersion IS NOT NULL);
GO

Related content