sql check约束_常用SQL Server约束：FOREIGN KEY，CHECK和DEFAULT

最新推荐文章于 2022-08-19 11:05:37 发布

culuo4781

最新推荐文章于 2022-08-19 11:05:37 发布

阅读量2.6k

点赞数

文章标签：数据库 python mysql java linux

原文链接：https://www.sqlshack.com/commonly-used-sql-server-constraints-foreign-key-check-default/

版权

sql check约束

In the previous article Commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY, we described, in detail, the first three types of the SQL Server constraints; NOT NULL, UNIQUE and PRIMARY KEY. In this article, we will discuss the other three constraints; FOREIGN KEY, CHECK and DEFAULT by describing each one briefly and providing practical examples.

在上一篇文章“ 常用SQL Server约束：NOT NULL，UNIQUE和PRIMARY KEY”中，我们详细介绍了SQL Server约束的前三种类型；即：非空，唯一和主键。在本文中，我们将讨论其他三个约束。通过简要描述每一个并提供实际示例，对外键，检查和默认设置。

外键约束 (FOREIGN KEY Constraint)

A Foreign Key is a database key that is used to link two tables together. The FOREIGN KEY constraint identifies the relationships between the database tables by referencing a column, or set of columns, in the Child table that contains the foreign key, to the PRIMARY KEY column or set of columns, in the Parent table.

外键是用于将两个表链接在一起的数据库键。 FOREIGN KEY约束通过将包含外键的子表中的一个列或一组列引用到父表中的PRIMARY KEY列或一组列，来标识数据库表之间的关系。

The relationship between the child and the parent tables is maintained by checking the existence of the child table FOREIGN KEY values in the referenced parent table’s PRIMARY KEY before inserting these values into the child table. In this way, the FOREIGN KEY constraint, in the child table that references the PRIMARY KEY in the parent table, will enforce database referential integrity. Referential integrity ensures that the relationship between the database tables is preserved during the data insertion process. Recall that the PRIMARY KEY constraint guarantees that no NULL or duplicate values for the selected column or columns will be inserted into that table, enforcing the entity integrity for that table. The entity integrity enforced by the PRIMARY KEY and the referential integrity enforced by the FOREIGN KEY together form the key integrity.

子表与父表之间的关系是通过在将子表的FOREIGN KEY值插入到子表中之前检查子表的FOREIGN KEY值的存在来维护的。这样，在子表中引用父表中PRIMARY KEY的FOREIGN KEY约束将强制执行数据库引用完整性。参照完整性可确保在数据插入过程中保留数据库表之间的关系。回想一下，PRIMARY KEY约束保证不会将任何NULL或所选列的重复值插入该表，从而增强了该表的实体完整性。由PRIMARY KEY强制执行的实体完整性和由FOREIGN KEY强制执行的引用完整性共同形成了密钥完整性。

The FOREIGN KEY constraint differs from the PRIMARY KEY constraint in that, you can create only one PRIMARY KEY per each table, with the ability to create multiple FOREIGN KEY constraints in each table by referencing multiple parent table. Another difference is that the FOREIGN KEY allows inserting NULL values if there is no NOT NULL constraint defined on this key, but the PRIMARY KEY does not accept NULLs.

FOREIGN KEY约束与PRIMARY KEY约束的不同之处在于，每个表只能创建一个PRIMARY KEY，并且能够通过引用多个父表在每个表中创建多个FOREIGN KEY约束。另一个区别是，如果在此密钥上未定义NOT NULL约束，则FOREIGN KEY允许插入NULL值，但是PRIMARY KEY不接受NULL。

The FOREIGN KEY constraint provides you also with the ability to control what action will be taken when the referenced value in the parent table is updated or deleted, using the ON UPDATE and ON DELETE clauses. The supported actions that can be taken when deleting or updating the parent table’s values include:

FOREIGN KEY约束还使您能够使用ON UPDATE和ON DELETE子句来控制在更新或删除父表中的引用值时将采取何种操作。删除或更新父表的值时可以采取的支持的操作包括：

NO ACTION: When the ON UPDATE or ON DELETE clauses are set to NO ACTION, the performed update or delete operation in the parent table will fail with an error. NO ACTION ：当ON UPDATE或ON DELETE子句设置为NO ACTION时，在父表中执行的更新或删除操作将失败，并显示错误。
CASCADE: Setting the ON UPDATE or ON DELETE clauses to CASCADE, the same action performed on the referenced values of the parent table will be reflected to the related values in the child table. For example, if the referenced value is deleted in the parent table, all related rows in the child table are also deleted. CASCADE ：将ON UPDATE或ON DELETE子句设置为CASCADE，对父表的引用值执行的相同操作将反映到子表中的相关值。例如，如果在父表中删除了引用的值，则子表中的所有相关行也将被删除。
SET NULL: With this ON UPDATE and ON DELETE clauses option, if the referenced values in the parent table are deleted or modified, all related values in the child table are set to NULL value. SET NULL ：使用此ON UPDATE和ON DELETE子句选项，如果父表中的引用值被删除或修改，则子表中的所有相关值都将设置为NULL值。
SET DEFAULT: Using the SET DEFAULT option of the ON UPDATE and ON DELETE clauses specifies that, if the referenced values in the parent table are updated or deleted, the related values in the child table with FOREIGN KEY columns will be set to its default value. SET DEFAULT ：使用ON UPDATE和ON DELETE子句的SET DEFAULT选项指定，如果父表中的引用值被更新或删除，则子表中带有FOREIGN KEY列的相关值将被设置为其默认值。。

You can add the FOREIGN KEY constraint while defining the column using the CREATE TABLE T-SQL statement, or add it after the table creation using the ALTER TABLE T-SQL statement. We will create two new tables to understand the FOREIGN KEY constraint functionality. The first table will act as the parent table with the ID column defined as a PRIMARY KEY column. The second table will act as the child table, with the ID column defined as the FOREIGN KEY column that references the ID column on the parent table. This can be achieved using the T-SQL script below:

您可以在使用CREATE TABLE T-SQL语句定义列的同时添加FOREIGN KEY约束，或者在使用ALTER TABLE T-SQL语句创建表之后将其添加。我们将创建两个新表来了解FOREIGN KEY约束功能。第一个表将用作父表，其ID列定义为PRIMARY KEY列。第二个表将用作子表，其ID列定义为引用父表的ID列的FOREIGN KEY列。这可以使用下面的T-SQL脚本来实现：

 
USE SQLShackDemo
GO
CREATE TABLE ConstraintDemoParent
(
       ID INT PRIMARY KEY,
	 Name VARCHAR(50) NULL
)
GO
CREATE TABLE ConstraintDemoChild
(
        CID INT PRIMARY KEY,
	 ID INT FOREIGN KEY REFERENCES ConstraintDemoParent(ID)
)

After creating the two tables, we will insert three records to the parent table, and two records to the child table, using the following INSERT statements:

创建两个表之后，我们将使用以下INSERT语句将三个记录插入父表，并将两个记录插入子表：

 
INSERT INTO ConstraintDemoParent ([ID],[NAME]) VALUES (1,'John'),(2,'Mika'),(3,'Sanya')
GO
INSERT INTO ConstraintDemoChild (CID,ID) VALUES (1,1)
GO
INSERT INTO ConstraintDemoChild (CID,ID) VALUES (2,4)
GO

The result will show you that the three records are successfully inserted into the parent table. The first record that we tried to insert into the child table is inserted without any error as the ID value of 1 already exists in the parent table. Trying to insert the second record into the child table will fail because the ID value of 4 doesn’t exist in the parent table, and due to the FOREIGN KEY constraint, you will not be able to insert an ID value to the child table that doesn’t exist in the parent table:

结果将向您显示这三个记录已成功插入到父表中。我们尝试插入子表中的第一条记录被插入而没有任何错误，因为父表中已经存在ID值1。尝试将第二条记录插入子表将失败，因为父表中不存在ID值4，并且由于FOREIGN KEY约束，您将无法将ID值插入到子表中父表中不存在：

Checking the parent and child tables content, you will see that only one record is inserted into the child table, as you can see below:

检查父表和子表的内容，您将看到仅一条记录插入到子表中，如下所示：

As we did not mention the FOREIGN KEY constraint name while creating the child table, SQL Server will assign it a unique name that we can retrieve from the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system view using the following query:

由于在创建子表时未提及FOREIGN KEY约束名称，因此SQL Server将为其分配一个唯一的名称，可以使用以下查询从INFORMATION_SCHEMA.TABLE_CONSTRAINTS系统视图中检索该名称：

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

The result in our case for the child table is as below:

在本例中，子表的结果如下：

Then we can easily use the previous result to drop the FOREIGN KEY constraint using the following
ALTER TABEL … DROP CONSTRAINT T-SQL statement:

然后，我们可以使用以下内容轻松地使用先前的结果来删除FOREIGN KEY约束
ALTER TABEL…DROP CONSTRAINT T-SQL语句：

 
ALTER TABLE ConstraintDemoChild
DROP CONSTRAINT  FK__ConstraintDe__ID__0B91BA14;

Trying to insert the second record into the child table again, the insert operation will succeed without any error as shown below:

尝试再次将第二条记录插入子表中时，插入操作将成功完成，而不会出现任何错误，如下所示：

But if we try to create the FOREIGN KEY constraint again on the ID column of the child table, using following ALTER TABLE T-SQL statement:

但是，如果我们尝试使用以下ALTER TABLE T-SQL语句在子表的ID列上再次创建FOREIGN KEY约束，则：

 
ALTER TABLE ConstraintDemoChild
ADD CONSTRAINT FK__ConstraintDe__ID
FOREIGN KEY (ID) REFERENCES ConstraintDemoParent(ID);

The operation will fail, as the ID value of 4 does not exist in the parent table and breaks the referential integrity between the child and parent tables, as seen in the following error message:

该操作将失败，因为父表中不存在ID值4，并且破坏了子表和父表之间的引用完整性，如以下错误消息所示：

To be able to create the FOREIGN KEY constraint in the child table, we have to eliminate that conflict first by deleting or updating that record. Here we will try to modify the ID value with the following UPDATE command:

为了能够在子表中创建FOREIGN KEY约束，我们必须首先通过删除或更新该记录来消除该冲突。在这里，我们将尝试使用以下UPDATE命令修改ID值：

 
UPDATE [dbo].[ConstraintDemoChild] SET ID =2  WHERE ID = 4

Now the FOREIGN KEY constraint will be created successfully without any error as shown below:

现在，FOREIGN KEY约束将成功创建，没有任何错误，如下所示：

A FOREIGN KEY constraint can be defined with the help of the SQL Server Management Studio tool. Right-click on the required table and select the Design option. When the Design window is displayed right-click on it and choose the Relationships option:

可以在SQL Server Management Studio工具的帮助下定义FOREIGN KEY约束。右键单击所需的表，然后选择“设计”选项。显示“设计”窗口后，右键单击它，然后选择“ 关系”选项：

From the displayed window, you can easily specify the name of the FOREIGN KEY constraint, the tables and columns that will participate in that relation, by clicking on the Tables And Columns Specification, if checking the existing data is required, and finally the action performed when the referenced record is deleted or modified on the parent table, as shown below:

在“显示的窗口”中，如果需要检查现有数据，然后单击“ 表和列”规范 ，则可以轻松地指定FOREIGN KEY约束的名称，将参与该关系的表和列 ，如果需要检查现有数据，最后执行操作在父表上删除或修改引用记录时，如下所示：

检查约束 (CHECK Constraint)

A CHECK constraint is defined on a column or set of columns to limit the range of values, that can be inserted into these columns, using a predefined condition. The CHECK constraint comes into action to evaluate the inserted or modified values, where the value that satisfies the condition will be inserted into the table, otherwise, the insert operation will be discarded. It is allowed to specify multiple CHECK constraints for the same column.

在一个列或一组列上定义了CHECK约束，以限制可以使用预定义条件插入到这些列中的值的范围。 CHECK约束用于评估插入或修改后的值，其中将满足条件的值插入表中，否则将放弃插入操作。可以为同一列指定多个CHECK约束。

Defining the CHECK constraint condition is somehow similar to writing the WHERE clause of a query, using the different comparison operators, such as AND, OR, BETWEEN, IN, LIKE and IS NULL to write its Boolean expression that will return TRUE, FALSE or UNKNOWN. The CHECK constraint will return UNKNOWN value when a NULL value is present in the condition. The CHECK constraint is used mainly to enforce the domain integrity by limiting the inserted values to the ones that follow the defined values, range or format rules.

定义CHECK约束条件在某种程度上类似于编写查询的WHERE子句，使用不同的比较运算符（例如AND，OR，BETWEEN，IN，LIKE和IS NULL）编写其布尔表达式，该布尔表达式将返回TRUE，FALSE或UNKNOWN 。当条件中存在NULL值时，CHECK约束将返回UNKNOWN值。 CHECK约束主要用于通过将插入的值限制为遵循定义的值，范围或格式规则的值来强制域完整性。

Let us create a new simple table that has three columns; the ID column that is considered as the PRIMARY KEY of that table, Name, and Salary. A CHECK constraint is defined on the Salary column to make sure that no zero or negative values are inserted into that column. The CHECK constraint is defined within CREATE TABLE T-SQL statement below:

让我们创建一个新的简单表，该表具有三列。 ID列，该列被视为该表的“主键”，“名称”和“薪水”。在Salary列上定义了CHECK约束，以确保没有零或负值插入该列。 CHECK约束在下面的CREATE TABLE T-SQL语句中定义：

 
CREATE TABLE ConstraintDemo4
(
     ID INT PRIMARY KEY,
	    Name VARCHAR(50) NULL,
	    Salary INT CHECK (Salary>0)
)
GO

If you execute the below three INSERT statements:

如果执行以下三个INSERT语句：

 
INSERT INTO ConstraintDemo4 ([ID],[NAME],Salary) VALUES (1,'John',350)
GO
INSERT INTO ConstraintDemo4 ([ID],[NAME],Salary) VALUES (2,'Mike',0)
GO
INSERT INTO ConstraintDemo4 ([ID],[NAME],Salary) VALUES (3,'Nikola',-72)
GO

It is clear from the generated result that the first record is inserted with no error as the provided Salary value meets the checking condition. The second and third INSERT statements will fail, as the provided Salary values do not meet the CHECK constraint condition due to inserting zero and negative Salary values, as you can see in the following error message:

从生成的结果很明显，因为提供的薪水值满足检查条件，所以没有错误地插入了第一条记录。第二个和第三个INSERT语句将失败，因为提供的Salary值由于插入零和负Salary值而不满足CHECK约束条件，如以下错误消息所示：

Checking the table’s data shows you that only the first row that passed the CHECK constraint condition will be inserted to the table as below:

检查表的数据显示，只有通过CHECK约束条件的第一行将被插入到表中，如下所示：

We mentioned earlier in this article that the CHECK constraint allows inserting NULL values if the participated columns allow NULL, and the NULL values will be evaluated as UNKNOWN without throwing any error. This is clear from the below record that is inserted successfully, although the provided value of the Salary column is NULL:

我们在本文前面提到，如果参与的列允许NULL，则CHECK约束允许插入NULL值，并且NULL值将被评估为UNKNOWN而不会引发任何错误。从下面的记录可以清楚地看到，尽管Salary列的提供值为NULL，但是已成功插入以下记录：

If you review the CHECK constraint definition in the previous CREATE TABLE statement, you will see that we have not mentioned the name of the defined constraint. In this case, SQL Server will assign a unique name for that constraint that can be shown by querying the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system view for the ConstraintDemo4 table. The result will be as shown below:

如果您在前面的CREATE TABLE语句中查看CHECK约束定义，您将看到我们没有提到已定义约束的名称。在这种情况下，SQL Server将为此约束分配一个唯一的名称，该名称可以通过查询ConstraintDemo4表的INFORMATION_SCHEMA.TABLE_CONSTRAINTS系统视图来显示。结果将如下所示：

The CHECK constraint can be dropped using the ALTER TABLE T-SQL statement. Using the CHCEK constraint name we got previously, the below command can be used to drop the CHECK constraint on the ConstraintDemo4 table:

可以使用ALTER TABLE T-SQL语句删除CHECK约束。使用我们之前获得的CHCEK约束名称，可以使用以下命令将CHECK约束放在ConstraintDemo4表上：

 
ALTER TABLE ConstraintDemo4
DROP CONSTRAINT  CK__Constrain__Salar__0F624AF8;

Once the CHECK constraint is dropped, the failed two INSERT statement will be executed successfully without any error, as there is no restriction on the inserted Salary values, as follows:

删除CHECK约束后，成功执行失败的两个INSERT语句而不会出现任何错误，因为对插入的Salary值没有限制，如下所示：

But if you try to define the CHECK constraint now, with the following ALTER DATABASE … ADD CONSTRAINT T-SQL statement:

但是，如果您现在尝试使用以下ALTER DATABASE…ADD CONSTRAINT T-SQL语句来定义CHECK约束：

 
ALTER TABLE ConstraintDemo4
ADD CONSTRAINT CK__Constrain__Salar
CHECK (Salary>0);

Adding the CHECK constraint will fail. This is due to the fact that, while the SQL Server is checking the already existing data for the CHECK constraint condition, one or more values that do not meet the constraint condition are found, as you can see in the following error message:

添加CHECK约束将失败。这是由于以下事实：在SQL Server检查CHECK约束条件的现有数据时，发现了一个或多个不满足约束条件的值，如以下错误消息所示：

To be able to define the CHECK constraint again, we should modify or delete the data that prevents the constraint from being created. We will try modifying the Salary amount of the two records that break the constraint condition to a valid positive value, as in the following UPDATE statement:

为了能够再次定义CHECK约束，我们应该修改或删除阻止创建约束的数据。我们将尝试将打破约束条件的两个记录的Salary数量修改为有效的正值，如以下UPDATE语句所示：

 
UPDATE ConstraintDemo4 SET Salary =300 WHERE ID IN (2,3)

Now, the CHECK constraint can be defined with no issue as shown below:

现在，可以毫无问题地定义CHECK约束，如下所示：

SQL Server allows you to disable the CHECK constraint for special cases, such as inserting huge amount of data without caring about meeting the constraint condition for testing purposes, or due to changing in the business logic. This case is valid only for the CHECK and FOREIGN KEY constraints that you can disable temporarily. Disabling the previously defined CHECK constraint can be achieved using the ALTER TABLE T-SQL command below:

SQL Server允许您在特殊情况下禁用CHECK约束，例如插入大量数据而不必担心出于测试目的或由于业务逻辑更改而满足约束条件的情况。这种情况仅对您可以暂时禁用的CHECK和FOREIGN KEY约束有效。可以使用下面的ALTER TABLE T-SQL命令来禁用先前定义的CHECK约束：

 
ALTER TABLE ConstraintDemo4 NOCHECK CONSTRAINT CK__Constrain__Salar

Trying to insert a negative Salary value will not fail, as there is no validation check performed on that column, as shown below:

尝试插入负的Salary值不会失败，因为对该列没有执行验证检查，如下所示：

You can also disable all the table constraints using one command by providing the ALTER TABLE … NOCHECK CONSTRAINT command with ALL instead of the writing the constraint name, as in the T-SQL command below:

您还可以使用一个命令来禁用所有表约束，方法是为ALTER TABLE…NOCHECK CONSTRAINT命令提供ALL，而不是像下面的T-SQL命令那样编写约束名称：

 
ALTER TABLE ConstraintDemo4 NOCHECK CONSTRAINT ALL

From the CHECK constraint definition, you can specify that the constraint will be created without checking existing rows, but the constraint will be marked as not trusted. Let us check the table data first, that shows two breaking values:

从CHECK约束定义中，您可以指定将在不检查现有行的情况下创建约束，但是该约束将被标记为不可信。让我们首先检查表数据，该数据显示两个中断值：

If you try to drop the CHECK constraint and create it again using the NOCHECK option, you will see that the constraint is created without checking the existing data, as shown below:

如果尝试删除CHECK约束，然后使用NOCHECK选项再次创建它，则将看到该约束是在不检查现有数据的情况下创建的，如下所示：

But at the same time, it will not allow you to insert any data that breaks the constraint condition, as you can clearly see in the error message below:

但是同时，它不允许您插入任何违反约束条件的数据，如您在以下错误消息中可以清楚看到的那样：

To enable the CHECK constraint again, you can use the ALTER TABLE T-SQL command, but this time using CHECK CONSTRAINT statement as in the script below:

要再次启用CHECK约束，可以使用ALTER TABLE T-SQL命令，但是这次使用CHECK CONSTRAINT语句，如下面的脚本所示：

 
ALTER TABLE ConstraintDemo4 CHECK CONSTRAINT CK__Constrain__Salar

In addition, you can enable all CHECK constraints, all at once,using the T-SQL command below:

此外，您可以使用以下T-SQL命令一次全部启用所有CHECK约束：

 
ALTER TABLE ConstraintDemo4 CHECK CONSTRAINT ALL

While enabling the previous CHECK constraint, you will see that SQL Server will not complain about the unchecked data that breaks the constraint condition. In this case, domain integrity of the data is not maintained.

在启用先前的CHECK约束时，您将看到SQL Server将不会抱怨破坏约束条件的未经检查的数据。在这种情况下，不能保持数据的域完整性。

To fix this issue, the DBCC CHECKCONSTRAINTS command can be easily used to identify the data that violates the constraint condition in a specified table or constraint, taking into consideration not to run that command in the peak hours, as it will affect the SQL Server performance due to not utilizing a database snapshot.

若要解决此问题， DBCC CHECKCONSTRAINTS命令可以轻松地用于识别违反指定表或约束中的约束条件的数据，并考虑到不要在高峰时段运行该命令，因为它将影响SQL Server性能由于未利用数据库快照。

Let us use the DBCC CHECKCONSTRAINTS command to check the data that does not meet the condition of the ConstraintDemo4 table’s constraint

让我们使用DBCC CHECKCONSTRAINTS命令检查不符合ConstraintDemo4表约束条件的数据

 
DBCC CHECKCONSTRAINTS(ConstraintDemo4)

The result will show you the two Salary values that break the CHECK constraint condition, as shown below:

结果将显示两个打破CHECK约束条件的Salary值，如下所示：

A CHECK constraint can be also created with the help of the SQL Server Management Studio tool, by right-clicking on the required table and selecting the Design option. From the displayed Design window, right-click and choose Check Constraints option as follows:

也可以在SQL Server Management Studio工具的帮助下，通过右键单击所需表并选择“设计”选项来创建CHECK约束。在显示的“设计”窗口中，右键单击并选择“ 检查约束”选项，如下所示：

From the Check Constraints window, you can specify the CHECK constraint name, expression and if checking the existing data is required, as shown below:

在“检查约束”窗口中，可以指定“检查”约束名称，表达式以及是否需要检查现有数据，如下所示：

默认约束 (DEFAULT Constraint)

A DEFAULT constraint is used to provide a default column value for the inserted rows if no value is specified for that column in the INSERT statement. The Default constraint helps in maintaining the domain integrity by providing proper values for the column, in case the user does not provide a value for it. The default value can be a constant value, a system function value or NULL.

如果未在INSERT语句中为该列指定值，则使用DEFAULT约束为插入的行提供默认列值。如果用户未提供值，则Default约束通过为列提供适当的值来帮助维护域的完整性。默认值可以是常量值，系统函数值或NULL。

In the below CREATE TABLE statement for a simple table with three columns, a DEFAULT constraint is defined on the EmployeeDate column, that assigns the GETDATE() system function value for that column in case we miss specifying it in the INSERT statement:

在下面的包含三列的简单表的CREATE TABLE语句中，在EmployeeDate列上定义了DEFAULT约束，该约束为该列分配GETDATE（）系统函数值，以防万一我们在INSERT语句中没有指定它的情况：

 
CREATE TABLE ConstraintDemo5
(
        ID INT PRIMARY KEY,
	    Name VARCHAR(50) NULL,
	    EmployeeDate DATETIME NOT NULL DEFAULT GETDATE()
)
GO

If we execute the two INSERT statements below:

如果我们执行下面的两个INSERT语句：

 
INSERT INTO ConstraintDemo5 ([ID],[NAME],EmployeeDate) VALUES (1,'Lorance','2016/10/22')
GO
INSERT INTO ConstraintDemo5 ([ID],[NAME]) VALUES (2,'Shady')
GO

And check the inserted records, you will see that the EmployeeDate column value for the second record, that we did not mention in the INSERT statement, is assigned to the current date and time value as shown below:

并检查插入的记录，您将看到第二条记录的EmployeeDate列值（我们在INSERT语句中未提及）已分配给当前日期和时间值，如下所示：

Expanding the Constraints node under the current table will show us the created DEFAULT constraint name, recalling that SQL Server will assign a unique name for it if we do not provide a name for it, as shown below:

展开当前表下的Constraints节点，将向我们显示创建的DEFAULT约束名称，回想一下，如果我们不为其提供名称，SQL Server将为其分配一个唯一的名称，如下所示：

The DEFAULT constraint can be easily dropped using the ALTER TABLE … DROP CONSTRAINT T-SQL command below:

可以使用下面的ALTER TABLE…DROP CONSTRAINT T-SQL命令轻松删除DEFAULT约束：

 
ALTER TABLE ConstraintDemo5
DROP CONSTRAINT  DF__Constrain__Emplo__1332DBDC;

And created using the ALTER TABLE …ADD CONSTRAINT T-SQL command below:

并使用下面的ALTER TABLE…ADD CONSTRAINT T-SQL命令创建：

 
ALTER TABLE ConstraintDemo5
Add Constraint DF__Constrain__Emplo  DEFAULT (GETDATE()) FOR EmployeeDate

Also, the DEFAULT constraint can be defined using the SQL Server Management Studio, by right-clicking on the required table and choose Design option. Then select the column that you will assign a default value for by browsing the Column Properties window as shown below:

另外，可以使用SQL Server Management Studio通过右键单击所需表并选择“设计”选项来定义DEFAULT约束。然后通过浏览“列属性”窗口，选择要为其分配默认值的列，如下所示：

I hoped that this article, and the previous, have helped to explain the six SQL Server constraint types. Please feel free to ask any questions in the comments below.

我希望本文和以前的文章有助于解释六种SQL Server约束类型。请随时在下面的评论中提出任何问题。

The previous article in this series:

本系列的上一篇文章：

Commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY 常用SQL Server约束：NOT NULL，UNIQUE和PRIMARY KEY

有用的链接 (Useful links)

翻译自: https://www.sqlshack.com/commonly-used-sql-server-constraints-foreign-key-check-default/

sql check约束

culuo4781

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
sql check约束_常用SQL Server约束：FOREIGN KEY，CHECK和DEFAULT

sql check约束 In the previous article Commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY, we described, in detail, the first three types of the SQL Server constraints; NOT NULL, U...
复制链接

扫一扫