Create unique constraints
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
You can create a unique constraint in SQL Server by using SQL Server Management Studio or Transact-SQL 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
In Object Explorer, right-click the table to which you want to add a unique constraint, and select Design.
On the Table Designer menu, select Indexes/Keys.
In the Indexes/Keys dialog box, select Add.
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.
On the File menu, select Save table name.
Use Transact-SQL
Create a unique constraint using Transact-SQL
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
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 columnTransactionID
.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
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. The example creates a unique constraint on the columns
PasswordHash
andPasswordSalt
in the tablePerson.Password
.USE AdventureWorks2022; GO ALTER TABLE Person.Password ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt); GO
Create a unique constraint on a new table
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
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
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
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