Skip to content

create-unique-constraints.md

Latest commit

 

History

History
123 lines (84 loc) · 4.74 KB

create-unique-constraints.md

File metadata and controls

123 lines (84 loc) · 4.74 KB
title Create Unique Constraints | Microsoft Docs
ms.custom
ms.date 03/17/2020
ms.prod sql
ms.prod_service database-engine, sql-database
ms.reviewer
ms.technology table-view-index
ms.topic conceptual
f1_keywords
UNIQUE_TSQL
helpviewer_keywords
UNIQUE constraints [SQL Server], creating
constraints [SQL Server], creating
constraints [SQL Server], unique
ms.assetid a86f9d6f-f242-43be-b65d-b3435b71b62a
author stevestein
ms.author sstein
monikerRange =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Create Unique Constraints

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx-md]

You can create a unique constraint in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql] 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.

In This Topic

Before You Begin

Security

Permissions

Requires ALTER permission on the table.

Using 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 click Design.

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

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

  4. In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.

  5. On the File menu, click Savetable name.

Using Transact-SQL

To create a unique constraint

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

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click 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 [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click 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 in an new table

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

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click 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

    For more information, see ALTER TABLE (Transact-SQL), CREATE TABLE (Transact-SQL), and table_constraint (Transact-SQL).