Feedback Edit

Create Unique Constraints

  • Article
  • 2 minutes to read

Applies to: yesSQL Server 2016 (13.x) and later YesAzure SQL Database YesAzure 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 do not participate in a primary key. Creating a unique constraint automatically creates a corresponding unique index.

Note

See Primary key, foreign key, and unique key in Azure Synapse Analytics for information on unique constraints in Azure Synapse Analytics.

Permissions

Requires ALTER permission on the table.

Use SQL Server Management Studio

To create a unique constraint

  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 drop-down list box to the right of the property, and then select Close.

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

Use Transact-SQL

To create a unique constraint

  1. In Object Explorer, connect to an instance of Database Engine.

  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 AdventureWorks2012;  
GO  
CREATE TABLE Production.TransactionHistoryArchive4  
 (  
   TransactionID int NOT NULL,   
   CONSTRAINT AK_TransactionID UNIQUE(TransactionID)   
);   
GO

To create a unique constraint on an existing table

  1. In Object Explorer, connect to an instance of Database Engine.

  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 AdventureWorks2012;   
GO  
ALTER TABLE Person.Password   
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);   
GO

To create a unique constraint on a new table

  1. In Object Explorer, connect to an instance of Database Engine.

  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 AdventureWorks2012;  
GO  
CREATE TABLE Production.TransactionHistoryArchive2  
(  
   TransactionID int NOT NULL,  
   CONSTRAINT AK_TransactionID UNIQUE(TransactionID)  
);  
GO

Next steps