SQLServer中唯⼀索引和唯⼀约束之间的区别
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, to learn about ACID properties.
您可以阅读本⽂“ 以了解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 Table
s-> New->Table.
让我们使⽤SSMS GUI⽅法创建⼀个SQL表。 展开数据库，然后右键单击Tables-> New-> Table。
Specify columns, their data type and remove the check for the Allow Nulls column.
指定列，其数据类型，并取消选中“ 允许空值”列。
Right-click on the [EmployeeID] column and enable the Primary Key by clicking Set Primary key on it.右键单击[EmployeeID]列，然后单击“ 设置主键”以启⽤主键 。
It puts a key symbol for the primary key column, as shown below.
它在主键列中放置⼀个键符号，如下所⽰。
Now, right-click on the [SocialSecurityNumber] column and choose Indexes/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 the General group, 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选择⼀个值。
创建唯一约束sql语句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 the Generate 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中创建表和唯⼀约束。
我修改了表名，使其具有适合我们演⽰的名称。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。