SQL Server中的约束：SQL NOT NULL，UNIQUE和SQL PRIMARY KEY

This article explains the SQL NOT NULL, Unique and SQL Primary Key constraints in SQL Server with examples.

本文通过示例解释SQL Server中SQL NOT NULL，唯一和SQL主键约束。

Constraints in SQL Server are predefined rules and restrictions that are enforced in a single column or multiple columns, regarding the values allowed in the columns, to maintain the integrity, accuracy, and reliability of that column’s data. In other words, if the inserted data meets the constraint rule, it will be inserted successfully. If the inserted data violates the defined constraint, the insert operation will be aborted.

SQL Server中的约束是预定义的规则和限制，它们在单列或多列中强制执行，与列中允许的值有关，以维护该列数据的完整性，准确性和可靠性。 换句话说，如果插入的数据符合约束规则，则将成功插入。 如果插入的数据违反定义的约束，则插入操作将被中止。

Constraints in SQL Server can be defined at the column level, where it is specified as part of the column definition and will be applied to that column only, or declared independently at the table level. In this case, the constraint rules will be applied to more than one column in the specified table. The constraint can be created within the CREATE TABLE T-SQL command while creating the table or added using ALTER TABLE T-SQL command after creating the table. Adding the constraint after creating the table, the existing data will be checked for the constraint rule before creating that constraint.

可以在列级别定义SQL Server中的约束，在该约束中将其指定为列定义的一部分，并将仅应用于该列，或者在表级别独立声明。 在这种情况下，约束规则将应用于指定表中的多个列。 可以在创建表时在CREATE TABLE T-SQL命令中创建约束，也可以在创建表后使用ALTER TABLE T-SQL命令添加约束。 在创建表之后添加约束，将在创建该约束之前检查现有数据中的约束规则。

There are six main constraints that are commonly used in SQL Server that we will describe deeply with examples within this article and the next one. These constraints are:

SQL Server中通常有六个主要约束条件，我们将在本文和下一个示例中进行深入描述。 这些约束是：

• SQL NOT NULL
  SQL非空
• UNIQUE
  独特
• PRIMARY KEY
  首要的关键
• FOREIGN KEY
  外键
• CHECK
  检查
• DEFAULT
  默认

In this article, we will go through the first three constraints; SQL NOT NULL, UNIQUE and SQL PRIMARY KEY, and we will complete the rest three constraints in the next article. Let us start discussing each one of these SQL Server constraints with a brief description and practical demo.

在本文中，我们将经历前三个约束； SQL NOT NULL，UNIQUE和SQL PRIMARY KEY，我们将在下一篇文章中完成其余三个约束。 让我们从简短的说明和实际演示开始讨论这些SQL Server约束中的每一个。

SQL中的NOT NULL约束 (NOT NULL Constraint in SQL)

By default, the columns are able to hold NULL values. A NOT NULL constraint in SQL is used to prevent inserting NULL values into the specified column, considering it as a not accepted value for that column. This means that you should provide a valid SQL NOT NULL value to that column in the INSERT or UPDATE statements, as the column will always contain data.

默认情况下，这些列能够保存NULL值。 SQL中的NOT NULL约束用于防止将NULL值插入指定的列，并将其视为该列的不可接受值。 这意味着您应该在INSERT或UPDATE语句中为该列提供有效SQL NOT NULL值，因为该列将始终包含数据。

Assume that we have the below simple CREATE TABLE statement that is used to define the ConstraintDemo1 table. This table contains only two columns, ID and Name. In the ID column definition statement, the SQL NOT NULL column-level constraint is enforced, considering the ID column as a mandatory column that should be provided with a valid SQL NOT NULL value. The case is different for the Name column that can be ignored in the INSERT statement, with the ability to provide it with NULL value. If the null-ability is not specified while defining the column, it will accept the NULL value by default:

假设我们有以下简单的CREATE TABLE语句，用于定义ConstraintDemo1表。 该表仅包含两列，ID和Name。 在ID列定义语句中，考虑将ID列视为应提供有效SQL NOT NULL值的强制列，因此将强制执行SQL NOT NULL列级约束。 可以在INSERT语句中忽略的Name列的大小写有所不同，并且可以为其提供NULL值。 如果在定义列时未指定可空性，则默认情况下它将接受NULL值：

 
USE SQLShackDemo
GO
CREATE TABLE ConstraintDemo1
(
       ID INT NOT NULL,
   Name VARCHAR(50) NULL
)
 

If we try to perform the below three insert operations:

如果我们尝试执行以下三个插入操作：

 
INSERT INTO ConstraintDemo1 ([ID],[NAME]) VALUES (1,'Ali')
GO
INSERT INTO ConstraintDemo1 ([ID]) VALUES (2)
GO
INSERT INTO ConstraintDemo1 ([NAME]) VALUES ('Fadi')
GO
 

You will see that the first record will be inserted successfully, as both the ID and Name column’s values are provided in the INSERT statement. Providing the ID only in the second INSERT statement will not prevent the insertion process from being completed successfully, due to the fact that the Name column is not mandatory and accepts NULL values. The last insert operation will fail, as we only provide the INSERT statement with a value for the Name column, without providing value for the ID column that is mandatory and cannot be assigned NULL value, as shown in the error message below:

您将看到第一条记录将被成功插入，因为INSERT语句中同时提供了ID和Name列的值。 仅在第二个INSERT语句中提供ID不会阻止插入过程成功完成，因为Name列不是强制性的并且接受NULL值。 最后的插入操作将失败，因为我们仅为INSERT语句提供Name列的值，而没有提供ID列的值，该值是强制性的并且不能分配NULL值，如以下错误消息所示：

Checking the inserted data, you will see that only two records are inserted and the missing value for the Name column in the second INSERT statement will be NULL, which is the default value, as shown in the result below:

检查插入的数据，您将看到仅插入了两条记录，第二条INSERT语句中Name列的缺失值将为NULL，这是默认值，如以下结果所示：

Assume that we need to prevent the Name column on the previous table from accepting NULL values after creating the table, using the ALTER TABLE T-SQL statement below:

假定我们需要使用下面的ALTER TABLE T-SQL语句防止上表的Name列在创建表后接受NULL值：

 
ALTER TABLE ConstraintDemo1 ALTER COLUMN [Name] VARCHAR(50) NOT NULL
 

You will see that the command will fail, as it will check the existing values of the Name column for NULL values first before creating the constraint, as shown in the error message below:

您将看到该命令将失败，因为它将在创建约束之前首先检查“名称”列的现有值是否为NULL值，如以下错误消息所示：

To enforce the NOT NULL Constraints in SQL, we should remove all NULL values of the Name column from the table, using the UPDATE statement below, that replaces the NULL values with empty string:

要在SQL中强制执行NOT NULL约束，我们应该使用下面的UPDATE语句从表中删除Name列的所有NULL值，该语句用空字符串替换NULL值：

 
UPDATE ConstraintDemo1 SET [Name]='' WHERE [Name] IS NULL
 

If you try to create the Constraints in SQL again, it will be created successfully as shown below:

如果尝试再次在SQL中创建约束，它将成功创建，如下所示：

The SQL NOT NULL constraint can be also created using the SQL Server Management Studio, by right-clicking on the needed table and select the Design option. Beside each column, you will find a small checkbox that you can use to specify the null-ability of that column. Unchecking the checkbox beside the column, a SQL NOT NULL constraint will be created automatically, preventing any NULL value from being inserted to that column, as shown below:

还可以使用SQL Server Management Studio通过右键单击所需表并选择“设计”选项来创建SQL NOT NULL约束。 在每列旁边，您会发现一个小的复选框，可用于指定该列的可空性。 取消选中该列旁边的复选框，将自动创建一个SQL NOT NULL约束，从而防止将任何NULL值插入该列，如下所示：

SQL中的唯一约束 (UNIQUE Constraints in SQL)

The UNIQUE constraint in SQL is used to ensure that no duplicate values will be inserted into a specific column or combination of columns that are participating in the UNIQUE constraint and not part of the PRIMARY KEY. In other words, the index that is automatically created when you define a UNIQUE constraint will guarantee that no two rows in that table can have the same value for the columns participating in that index, with the ability to insert only one unique NULL value to these columns, if the column allows NULL.

SQL中的UNIQUE约束用于确保不会将重复的值插入到参与UNIQUE约束且不属于PRIMARY KEY的特定列或列组合中。 换句话说，当您定义UNIQUE约束时自动创建的索引将保证该表中的任何两行对于该索引中的列都不能具有相同的值，并且能够向其中插入一个唯一的NULL值列（如果该列允许使用NULL）。

Let us create a small table with two columns, ID and Name. The ID column cannot hold duplicate values due to the UNIQUE constraint specified with the column definition. No restriction defined on the Name column, as in the CREATE TABLE T-SQL statement below:

让我们创建一个包含两列ID和Name的小表。 由于使用列定义指定了UNIQUE约束，所以ID列无法保存重复值。 在“名称”列上没有定义任何限制，如下面的CREATE TABLE T-SQL语句所示：

 
USE SQLShackDemo
GO
CREATE TABLE ConstraintDemo2
(
     ID INT UNIQUE,
   Name VARCHAR(50) NULL
)
 

If we try to run the four INSERT statements below:

如果我们尝试运行以下四个INSERT语句：

 
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (1,'Ali')
GO
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (2,'Ali')
GO
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (NULL,'Adel')
GO
INSERT INTO ConstraintDemo2 ([ID],[NAME]) VALUES (1,'Faris')
GO
 

The first two records will be inserted successfully, with no constraint preventing duplicate values of the Name column. The third record will be inserted successfully too, as the unique ID column allows only one NULL value. The last INSERT statement will fail, as the ID column doesn’t allow duplicate values and the provided ID value is already inserted to that column, as shown in the error message below:

前两个记录将成功插入，并且没有任何约束，可以防止Name列的值重复。 第三条记录也将成功插入，因为唯一ID列仅允许一个NULL值。 最后一个INSERT语句将失败，因为ID列不允许重复值，并且提供的ID值已插入到该列，如以下错误消息所示：

The three inserted rows will be as shown below:

插入的三行如下所示：

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS system object can be easily used to retrieve information about all defined constraints in a specific table using the T-SQL script below:

使用下面的T-SQL脚本，可以轻松地使用INFORMATION_SCHEMA.TABLE_CONSTRAINTS系统对象来检索有关特定表中所有已定义约束的信息：

 
SELECT CONSTRAINT_NAME,
     TABLE_SCHEMA ,
     TABLE_NAME,
     CONSTRAINT_TYPE
     FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE TABLE_NAME='ConstraintDemo2'
 

The previous query result will show the defined UNIQUE constraint in SQL in the provided table, which will be like:

上一个查询结果将在提供的表中显示SQL中定义的UNIQUE约束，如下所示：

Using the constraint name retrieved from the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system object, we can drop the UNIQUE constraint using the ALTER TABLE…DROP CONSTRAINT in SQL T-SQL command below:

使用从INFORMATION_SCHEMA.TABLE_CONSTRAINTS系统对象检索到的约束名称，我们可以在下面SQL T-SQL命令中使用ALTER TABLE…DROP CONSTRAINT删除UNIQUE约束：

 
ALTER TABLE ConstraintDemo2 DROP CONSTRAINT [UQ__Constrai__3214EC26B928E528]
 

If you try to run the previously failed INSERT statement, the record with duplicate ID value will be inserted successfully:

如果您尝试运行以前失败的INSERT语句，则将成功插入具有重复ID值的记录：

Trying to add the UNIQUE constraint in SQL again using the ALTER TABLE…ADD CONSTRAINT T-SQL command below:

尝试使用下面的ALTER TABLE…ADD CONSTRAINT T-SQL命令再次在SQL中添加UNIQUE约束：

 
ALTER TABLE ConstraintDemo2 ADD CONSTRAINT UQ__Constrai UNIQUE (ID)
GO
 

The constraint in SQL creation will fail, due to having duplicate values of that column in the table, as shown in the error message below:

由于表中该列的值重复，因此SQL创建约束将失败，如以下错误消息所示：

Checking the inserted data, the duplicate values will be clear as shown below:

检查插入的数据，将清除重复的值，如下所示：

In order to add the UNIQUE constraint, you have the choice of deleting or modifying the duplicate values. In our case, we will update the second duplicate ID value using the UPDATE statement below:

为了添加UNIQUE约束，您可以选择删除或修改重复值。 在我们的情况下，我们将使用下面的UPDATE语句更新第二个重复的ID值：

 
UPDATE [SQLShackDemo].[dbo].[ConstraintDemo2] SET ID =3 WHERE NAME='FARIS'
 

Now, the UNIQUE constraint in SQL can be added to the ID column with no error as below:

现在，可以将SQL中的UNIQUE约束无误添加到ID列，如下所示：

The UNIQUE key can be viewed using SQL Server Management Studio, by expanding the Keys node under the selected table. You can also see the automatically created index that is used to guarantee the column values uniqueness. Note that you will not be able to drop that index without dropping the UNIQUE constraint first:

通过展开所选表下的“密钥”节点，可以使用SQL Server Management Studio查看UNIQUE密钥。 您还可以看到自动创建的索引，该索引用于保证列值的唯一性。 请注意，您必须先删除UNIQUE约束才能删除该索引：

In addition to the previously shown T-SQL commands, the UNIQUE constraint can be also defined and modified using the SQL Server Management Studio. Right-click on the needed table and choose Design. From the Design window, right-click on that window and choose Indexes/Keys, from where you can mark the constraint as UNIQUE, as shown below:

除了前面显示的T-SQL命令之外，还可以使用SQL Server Management Studio定义和修改UNIQUE约束。 右键单击所需的表，然后选择“设计”。 在“设计”窗口中，右键单击该窗口，然后选择“索引/键”，从中可以将约束标记为“唯一”，如下所示：

SQL主键约束 (SQL PRIMARY KEY Constraint)

The PRIMARY KEY constraint consists of one column or multiple columns with values that uniquely identify each row in the table.

PRIMARY KEY约束由一列或多列组成，这些列的值唯一地标识表中的每一行。

The SQL PRIMARY KEY constraint combines between the UNIQUE and SQL NOT NULL constraints, where the column or set of columns that are participating in the PRIMARY KEY cannot accept a NULL value. If the PRIMARY KEY is defined in multiple columns, you can insert duplicate values on each column individually, but the combination values of all PRIMARY KEY columns must be unique. Take into consideration that you can define only one PRIMARY KEY per each table, and it is recommended to use small or INT columns in the PRIMARY KEY.

SQL PRIMARY KEY约束在UNIQUE和SQL NOT NULL约束之间进行组合，其中参与PRIMARY KEY的列或列集不能接受NULL值。 如果在多个列中定义了PRIMARY KEY，则可以在每个列上分别插入重复的值，但是所有PRIMARY KEY列的组合值必须唯一。 考虑到每个表只能定义一个PRIMARY KEY，建议在PRIMARY KEY中使用小或INT列。

In addition to providing fast access to the table data, the index that is automatically created, when defining the SQL PRIMARY KEY, will enforce the data uniqueness. The PRIMARY KEY is used mainly to enforce the entity integrity of the table. Entity integrity ensures that each row in the table is a uniquely identifiable entity.

除了提供对表数据的快速访问之外，在定义SQL PRIMARY KEY时自动创建的索引将强制数据唯一性。 PRIMARY KEY主要用于增强表的实体完整性。 实体完整性可确保表中的每一行都是唯一可识别的实体。

PRIMARY KEY constraint differs from the UNIQUE constraint in that; you can create multiple UNIQUE constraints in a table, with the ability to define only one SQL PRIMARY KEY per each table. Another difference is that the UNIQUE constraint allows for one NULL value, but the PRIMARY KEY does not allow NULL values.

PRIMARY KEY约束与UNIQUE约束的不同之处在于； 您可以在一个表中创建多个UNIQUE约束，并且每个表只能定义一个SQL PRIMARY KEY。 另一个区别是UNIQUE约束允许一个NULL值，但是PRIMARY KEY不允许NULL值。

Assume that we have the below simple table with two columns; the ID and Name. The ID column is defined as a PRIMARY KEY for that table, that is used to identify each row on that table by ensuring that no NULL or duplicate values will be inserted to that ID column. The table is defined using the CREATE TABLE T-SQL script below:

假设我们有下面的简单表，其中有两列； ID和名称。 ID列定义为该表的PRIMARY KEY，用于确保该表的每一行都确保没有NULL或重复值插入该ID列。 该表是使用下面的CREATE TABLE T-SQL脚本定义的：

 
USE SQLShackDemo
GO
CREATE TABLE ConstraintDemo3
(
     ID INT PRIMARY KEY,
   Name VARCHAR(50) NULL
)
 

If you try to run the three INSERT statements below:

如果您尝试运行下面的三个INSERT语句：

 
INSERT INTO ConstraintDemo3 ([ID],[NAME]) VALUES (1,'John')
GO
INSERT INTO ConstraintDemo3 ([NAME]) VALUES ('Fadi')
GO
INSERT INTO ConstraintDemo3 ([ID],[NAME]) VALUES (1,'Saeed')
GO
 

You will see that the first record will be inserted successfully as both the ID and Name values are valid. The second insert operation will fail, as the ID column is mandatory and cannot be NULL, as the ID column is the SQL PRIMARY KEY. The last INSERT statement will fail too as the provided ID value already exists and the duplicate values are not allowed in the PRIMARY KEY, as shown in the error message below:

您将看到第一条记录将成功插入，因为ID和Name值均有效。 第二个插入操作将失败，因为ID列是必需的，并且不能为NULL，因为ID列是SQL PRIMARY KEY。 最后一个INSERT语句也会失败，因为提供的ID值已经存在，并且PRIMARY KEY中不允许重复的值，如以下错误消息所示：

Checking the inserted values, you will see that only the first record is inserted successfully as below:

检查插入的值，您将看到仅成功插入了第一条记录，如下所示：

If you do not provide the SQL PRIMARY KEY constraint with a name during the table definition, the SQL Server Engine will provide it with a unique name as you can see from querying the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system object below:

如果在表定义期间未为SQL PRIMARY KEY约束提供名称，则SQL Server Engine将为它提供一个唯一名称，如从查询下面的INFORMATION_SCHEMA.TABLE_CONSTRAINTS系统对象可以看到的：

 
SELECT CONSTRAINT_NAME,
     TABLE_SCHEMA ,
     TABLE_NAME,
     CONSTRAINT_TYPE
     FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE TABLE_NAME='ConstraintDemo3'
 

With the below result in our example:

在我们的示例中显示以下结果：

The ALTER TABLE…DROP CONSTRAINT T-SQL statement can be used easily to drop the previously defined PRIMARY KEY using the name derived from the previous result:

ALTER TABLE…DROP CONSTRAINT T-SQL语句可以很容易地使用从先前结果​​派生的名称删除先前定义的PRIMARY KEY：

 
ALTER TABLE ConstraintDemo3
DROP CONSTRAINT PK__Constrai__3214EC27E0BEB1C4;
 

If you try to execute the previously failed two INSERT statements, you will see that the first record will not be inserted as the ID column does not allow NULL values. The second record will be inserted successfully as these is nothing prevent the duplicate values from being inserted after dropping the SQL PRIMARY KEY, as shown below:

如果您尝试执行先前失败的两个INSERT语句，您将看到不会插入第一条记录，因为ID列不允许使用NULL值。 第二条记录将被成功插入，因为这些都不能防止在删除SQL PRIMARY KEY后插入重复的值，如下所示：

Trying to add the SQL PRIMARY KEY constraint again using the ALTER TABLE T-SQL query below:

尝试使用下面的ALTER TABLE T-SQL查询再次添加SQL PRIMARY KEY约束：

 
ALTER TABLE ConstraintDemo3
ADD PRIMARY KEY (ID);
 

The operation will fail, as while checking the existing ID values first for any NULL or duplicate values, SQL Server finds a duplicate ID value of 1 as shown in the error message below:

该操作将失败，因为在首先检查现有ID值是否为NULL或重复值时，SQL Server发现重复ID值为1，如以下错误消息所示：

Checking the table’s data will show you also the duplicate value:

检查表中的数据还将显示重复值：

In order to add the PRIMARY KEY constraint, we should clear the data first, by deleting or modifying the duplicate record. Here we will change the second record ID value using the UPDATE statement below:

为了添加PRIMARY KEY约束，我们应该先通过删除或修改重复记录来清除数据。 在这里，我们将使用下面的UPDATE语句更改第二个记录ID值：

 
UPDATE ConstraintDemo3 SET ID =2 WHERE NAME ='Saeed'
 

Then trying to add the SQL PRIMARY KEY, which will be created successfully now:

然后尝试添加现在将成功创建SQL PRIMARY KEY：

The SQL PRIMARY KEY constraint can be also defined and modified using SQL Server Management Studio. Right-click on your table and choose Design. From the Design window, right-click on the column or set of columns that will participate in the PRIMARY KEY constraint and Set PRIMARY KEY option, that will automatically uncheck the Allow NULLs checkbox, as shown below:

还可以使用SQL Server Management Studio定义和修改SQL PRIMARY KEY约束。 右键单击表，然后选择“设计”。 在“设计”窗口中，右键单击将参与“ PRIMARY KEY”约束和“ Set PRIMARY KEY”选项的列或一组列，这将自动取消选中“允许空值”复选框，如下所示：

Please check the next article in the series Commonly used SQL Server Constraints: FOREIGN KEY, CHECK and DEFAULT that describes other three SQL Server constraints.

请检查“ 常用SQL Server约束 ”系列中的下一篇文章：FOREIGN KEY，CHECK和DEFAULT ，它描述了其他三个SQL Server约束。

有用的链接 (Useful links)

• Constraints 约束条件
• UNIQUE Constraints 唯一约束
• PRIMARY KEY Constraints 主键约束
• The benefits, costs, and documentation of database constraints 数据库约束的好处，成本和文档

翻译自: https://www.sqlshack.com/commonly-used-sql-server-constraints-not-null-unique-primary-key/