This article gives you an overview of Unique Constraints in SQL and also the Unique SQL Server index. Along the way, we will look at the differences between them.
本文为您概述了SQL中的唯一约束以及唯一SQL Server索引。 一路上，我们将研究它们之间的差异。
介绍 (Introduction)
Constraints in SQL Server allows defining the rules at the column level in the SQL table. We can add a constraint using the Create table or Alter table statement. SQL Server enforces ACID properties – Atomicity, Consistency, Isolation and Durability for a SQL Server transaction.
SQL Server中的约束允许在SQL表的列级别定义规则。 我们可以使用Create table或Alter table语句添加约束。 SQL Server强制ACID属性-SQL Server事务的原子性，一致性，隔离性和持久性。
We use Constraints for the Consistency property of an ACID. It means that only valid data that satisfies the condition should exist in the database.
我们对ACID的Consistency属性使用Constraints。 这意味着数据库中应仅存在满足条件的有效数据。
You can go through this article, SQL Server Transaction Overview to learn about ACID properties.
您可以阅读本文“ SQL Server事务概述”以了解ACID属性。
In this article, we will explore SQL Server Unique Indexes and Unique constraints. We will also go over the difference between them.
在本文中，我们将探讨SQL Server唯一索引和唯一约束。 我们还将介绍它们之间的区别。
SQL Server中的UNIQUE约束概述 (Overview of UNIQUE constraints in SQL Server)
We can ensure unique value in a column of SQL Server. It can be either on a single column or a combination of columns. It prevents you from having duplicate values in columns tied with the unique constraint. You might be familiar with a primary key column that also enforces unique value in the column. We can have only one primary key per table in SQL Server.
我们可以确保SQL Server列中的唯一值。 它可以在单个列上，也可以在列的组合上。 这样可以防止在具有唯一性约束的列中具有重复的值。 您可能熟悉主键列，该主键列也强制该列中的唯一值。 在SQL Server中，每个表只能有一个主键。
Suppose you have an employee table and as its name suggests it holds all employee’s information. We have a primary key for the [EmployeeID] column. This table also holds the social security number of employees. We do not want any duplicate value in this social security number column. We do not have the option to define the primary key because our table already has it.
假设您有一个雇员表，顾名思义，它包含所有雇员的信息。 我们为[EmployeeID]列提供了一个主键。 该表还保存了雇员的社会保险号。 我们不希望在此社会保险号列中有任何重复的值。 我们没有选择定义主键的选项，因为我们的表已经具有主键。
Let’s create a SQL table using the SSMS GUI method. Expand the database and right-click on Tables-> New->Table.
让我们使用SSMS GUI方法创建一个SQL表。 展开数据库，然后右键单击Tables-> New-> Table。
Specify columns, their data type and remove the check for theAllow Nullscolumn.
指定列，其数据类型，并取消选中“允许空值”列。
Right-click on the [EmployeeID] column and enable the Primary Key by clickingSet Primary keyon it.
右键单击[EmployeeID]列，然后单击“设置主键”以启用主键。
It puts a key symbol for the primary key column, as shown below.
它在主键列中放置一个键符号，如下所示。
Now, right-click on the [SocialSecurityNumber] column and chooseIndexes/Keys.
现在，右键单击[SocialSecurityNumber]列，然后选择“索引/键”。
It opens the following indexes/keys wizard that shows existing indexes like we already have a primary key on [Employee] table.
它打开下面的索引/键向导，该向导显示现有索引，例如我们在[Employee]表上已经有一个主键。
Click on Add, and we can define additional index/constraints using this.
单击添加，我们可以使用它定义其他索引/约束。
In theGeneralgroup, select the column in which we want to define a SQL Server Index. We can select the data sort order in ascending (default) or descending order.
在“常规”组中，选择要在其中定义SQL Server索引的列。 我们可以选择数据升序（默认）或降序。
In this SQL Server index properties, we can select a value for the property-IsUnique.
在此SQL Server索引属性中，我们可以为属性IsUnique选择一个值。
In the type, you get an option to choose from the Unique key or Index.
在类型中，您可以选择从唯一键或索引中进行选择。
Let’s select the Unique Key, and you see that the previous option “Is Unique” is greyed out. We cannot make any change here because the unique key is for unique value in a column.
让我们选择“唯一键”，您会看到前面的选项“是唯一”显示为灰色。 我们无法在此处进行任何更改，因为唯一键是列中唯一值。
SSMS gives you the option to generate the script for the work you did on the GUI. It is a good thing, especially for a beginner to learn both GUI and t-SQL.
SSMS使您可以选择为在GUI上所做的工作生成脚本。 这是一件好事，特别是对于初学者而言，同时学习GUI和t-SQL。
Click on theGenerate Change Script…, and you get t-SQL for Create table, add primary key constraint and add the unique constraint in SQL Server.
单击Generate Change Script…，您将获得用于创建表的t-SQL，添加主键约束并在SQL Server中添加唯一约束。
Copy this script and close the table designer window without saving it. We use the generated script to create the table and unique constraint in SQL Server. I modified the table name to have an appropriate name for our demo.
复制此脚本并关闭表设计器窗口而不保存它。 我们使用生成的脚本在SQL Server中创建表和唯一约束。 我修改了表名，使其具有适合我们演示的名称。
BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONGOCREATE TABLE dbo.Employee(EmployeeID int NOT NULL,EmpName varchar(50) NOT NULL,SocialSecurityNumber int NOT NULL)ON [PRIMARY]GOALTER TABLE dbo.Employee ADD CONSTRAINTPK_Table_1 PRIMARY KEY CLUSTERED (EmployeeID) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOALTER TABLE dbo.Employee ADD CONSTRAINTIX_Unique_SSN UNIQUE NONCLUSTERED (SocialSecurityNumber) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOALTER TABLE dbo.Employee SET (LOCK_ESCALATION = TABLE)GOCOMMIT
Execute the above script, and it creates the table, primary key and unique key constraint. It creates the SQL Server index for primary key constraints and unique key constraints. We can check existing indexes on a table usingsys.sp_helpindexsystem stored procedure.
执行上述脚本，并创建表，主键和唯一键约束。 它为主键约束和唯一键约束创建SQL Server索引。 我们可以使用sys.sp_helpindex系统存储过程来检查表上的现有索引。
EXEC sys.sp_helpindex @objname = N'Employee'GO
Let’s try to insert few values in this table and see if we are allowed to enter duplicates in the [SocialSecurityNumber] column.
让我们尝试在此表中插入一些值，看看是否允许我们在[SocialSecurityNumber]列中输入重复项。
In the below query, Ram tries to enter the value in the [SocialSecurityNumber] column that is already available for the employee Raj.
在以下查询中，Ram尝试在[SocialSecurityNumber]列中输入可供员工Raj使用的值。
Insert into Employee values(1,'Raj',1111)Insert into Employee values(2,'Shyam',2222)Insert into Employee values(3,'Ram',1111)
It inserts the first two rows successfully, but for third-row, you get a message about the Unique key constraint violation. You get the duplicate value in the output as well. It helps you to figure out the problematic insert statement causing issues quickly.
它成功插入了前两行，但是对于第三行，您将收到一条有关唯一键约束冲突的消息。 您也会在输出中得到重复的值。 它可以帮助您快速找出引起问题的插入语句。
在SQL Server中禁用唯一约束 (Disabling Unique Constraints in SQL Server)
We can disable a unique constraint using the following ALTER table statement.
我们可以使用以下ALTER table语句禁用唯一约束。
ALTER TABLE EmployeeNOCHECK CONSTRAINT ALLGO
This command executed successfully.
该命令成功执行。
If we try to enter the duplicate value, still we get the same error message.
如果我们尝试输入重复的值，仍然会收到相同的错误消息。
We know that the unique constraint in SQL Server creates a unique SQL Server index as well. SQL Server allows us to disable an index as well without dropping it.
我们知道，SQL Server中的唯一约束也会创建唯一SQL Server索引。 SQL Server允许我们也禁用索引而不删除它。
Right-click on the SQL Server index that we wish to disable and click onDisableas shown below.
右键单击我们要禁用SQL Server索引，然后单击“禁用”，如下所示。
Alternatively, we can use the ALTER INDEX command and disable the SQL Server index. You need to specify the index name and table name in this query.
或者，我们可以使用ALTER INDEX命令并禁用SQL Server索引。 您需要在此查询中指定索引名称和表名称。
ALTER INDEX IX_Unique_SSN ON EmployeeDISABLE
It allows you to enter the duplicate value in the [SocialSecurityNumber] column.
它允许您在[SocialSecurityNumber]列中输入重复值。
We have a duplicate value in the table. Let’s enable the unique non-clustered Index. To enable the Index, we need to rebuild it.
表中有一个重复的值。 让我们启用唯一的非聚集索引。 要启用索引，我们需要重建它。
To rebuild an index, either right-click on Index and click onREBUILDfrom its properties.
要重建索引，请右键单击Index，然后从其属性中单击REBUILD。
We can also rebuild using the following ALTER INDEX command.
我们还可以使用以下ALTER INDEX命令进行重建。
USE [SQLShack]GOALTER INDEX [IX_Unique_SSN] ON [dbo].[Employee] REBUILD
We cannot enable the Index because a duplicate key exists, and the unique key constraint does not allow duplicates. It also gives you duplicate keys in the output.
我们无法启用索引，因为存在重复的键，并且唯一键约束不允许重复。 它还在输出中为您提供重复的键。
在SQL Server中删除唯一约束 (Drop unique constraints in SQL Server)
We cannot drop the unique Index created by the unique constraints. If we try to do so, it gives you the following error message. It does not allow an explicit drop index because the unique constraint is using the Index.
我们不能删除由唯一约束创建的唯一索引。 如果我们尝试这样做，它会为您提供以下错误消息。 它不允许显式删除索引，因为唯一约束正在使用索引。
DROP INDEX Employee.[IX_Unique_SSN]GO
We can drop the Index using the Alter Table Drop Constraint command. As we know, SQL Server creates an index with a unique constraint in SQL Server. This command drops the Index along with the constraint.
我们可以使用Alter Table Drop Constraint命令删除索引。 众所周知，SQL Server在SQL Server中创建具有唯一约束的索引。 此命令将索引和约束一起删除。
ALTER TABLE EmployeeDROP CONSTRAINT IX_Unique_SSN;
We can verify in the following screenshot that Index does not exist now.
我们可以在以下屏幕截图中验证索引现在不存在。
It provides you with an additional benefit that no one can accidentally delete the unique Index created by the unique constraint.
它为您提供了一个额外的好处，那就是没人可以意外删除由唯一约束创建的唯一索引。
SQL Server中的唯一索引 (Unique Index in SQL Server)
Previously we created unique constraints in SQL Server using the SSMS GUI method. We do not have any existing unique constraints for the [Employee] table.
以前，我们使用SSMS GUI方法在SQL Server中创建了唯一约束。 对于[Employee]表，我们没有任何现有的唯一约束。
Let’s right-click on [Employee] table and select Design. It opens the table designer as we saw earlier.
让我们右键单击[Employee]表并选择Design。 如我们先前所见，它打开了表设计器。
Right-click on [SocialSecurityNumber], select Index/keys, and choose Index from the Type column. For a unique index, select the valueYesfor the [Is Unique] column.
右键单击[SocialSecurityNumber]，选择“索引/键”，然后从“类型”列中选择“索引”。 对于唯一索引，在[Is Unique]列中选择值Yes。
We also have the few options enabled for unique Indexes such asIgnore duplicate keysandRe-compute statistics.
我们还为唯一索引启用了一些选项，例如忽略重复键和重新计算统计信息。
Close this index/keys page and generate the script. You can see it creates a unique non-clustered index for the [Employee] table.
关闭此索引/键页面并生成脚本。 您可以看到它为[Employee]表创建了一个唯一的非聚集索引。
Click Ok and save the modifications you did. You get the error message because it found a duplicate key for the [SocialSecurityNumber] column.
单击确定，然后保存所做的修改。 您收到错误消息，因为它为[SocialSecurityNumber]列找到了重复的键。
We can remove the duplicate, and it creates the Index for you.
我们可以删除重复项，它会为您创建索引。
SQL Server中唯一索引和唯一约束之间的区别 (Difference between Unique Indexes and Unique Constraints in SQL Server)
Both the unique index and unique constraint are similar, and there is no functional difference between them. Query optimizer also uses the Unique Index to create cost-optimized execution plans. The only difference is that you cannot directly drop the unique Index created by the unique constraint in SQL Server. You also get a few additional index options once you directly create a unique Index.
唯一索引和唯一约束都相似，并且它们之间没有功能上的区别。 查询优化器还使用唯一索引来创建成本优化的执行计划。 唯一的区别是您不能直接删除由SQL Server中的唯一性约束创建的唯一性索引。 直接创建唯一索引后，您还将获得一些其他索引选项。
As we know, constraints are like a business rule for data stored in SQL Server tables. You should create a unique constraint when you do not directly deal with the Index. However, you should not define a unique constraint and key on similar columns. It might slow down your queries, and you have duplicate indexes as well.
众所周知，约束就像是存储在SQL Server表中的数据的业务规则。 当您不直接处理索引时，应创建唯一约束。 但是，您不应在相似的列上定义唯一约束和键。 这可能会使查询速度变慢，并且索引也重复。
We cannot differentiate between a unique key and Index by looking at indexes in GUI. Both exist in the same index folder in a database.
我们无法通过查看GUI中的索引来区分唯一键和索引。 两者都存在于数据库的同一索引文件夹中。
However, SQL Server knows the difference. If we script out both Indexes, you can see different scripts for both indexes. As we can see below, the first script uses an alter table with Add Constraint clause for a unique constraint in SQL Server while the later part uses the Create Non-Clustered Index statement.
但是，SQL Server知道区别。 如果我们为两个索引编制脚本，则可以为两个索引看到不同的脚本。 正如我们在下面看到的那样，第一个脚本使用带有Add Constraint子句的alter table作为SQL Server中的唯一约束，而后一部分使用Create Non-Clustered Index语句。
Still, you get some additional advantages with unique Index created explicitly over Unique Constraints in SQL Server.
尽管如此，通过显式创建的唯一索引比SQL Server中的“唯一约束”还具有一些其他优点。
You can include columns in a non-clustered unique index to improve query performance. SQL Server enforces uniqueness only for the key column of a unique index 您可以在非聚集唯一索引中包括列，以提高查询性能。 SQL Server仅对唯一索引的键列强制执行唯一性 We can add a filter in a unique index. It can be useful if you want to create a unique index on a column that allows NULL values. In this case, we can have multiple NULL values because a unique Index will be created for non-null values 我们可以在唯一索引中添加过滤器。 如果要在允许NULL值的列上创建唯一索引，此功能很有用。 在这种情况下，我们可以有多个NULL值，因为将为非null值创建唯一索引 We can also define a foreign key that reference a unique key index 我们还可以定义引用唯一键索引的外键
结论 (Conclusion)
In this article, we explored unique constraints and unique indexes in SQL Server. We get a unique index as well if you create a unique constraint. You can decide whatever option works for you as there is no difference in query performance.
在本文中，我们探讨了SQL Server中的唯一约束和唯一索引。 如果您创建唯一约束，我们也会获得唯一索引。 您可以选择适合您的任何选项，因为查询性能没有差异。
翻译自: /difference-between-unique-indexes-and-unique-constraints-in-sql-server/
