创建 CHECK 约束Create Check Constraints

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

您可以通过使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio ,在表中创建 CHECK 约束以便指定在 Transact-SQLTransact-SQL的一列或多列中可接受的数据值。You can create a check constraint in a table to specify the data values that are acceptable in one or more columns in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题内容In This Topic

开始之前Before You Begin

SecuritySecurity

权限Permissions

需要具有表的 ALTER 权限。Requires ALTER permissions on the table.

使用 SQL Server Management StudioUsing SQL Server Management Studio

创建新的 CHECK 约束To create a new check constraint

  1. 在“对象资源管理器”中,展开要为其添加 CHECK 约束的表,右键单击“约束”,然后单击“新建约束”。In Object Explorer, expand the table to which you want to add a check constraint, right-click Constraints and click New Constraint.

  2. 在“CHECK 约束”对话框中,单击“表达式”字段,然后单击省略号 (…)。In the Check Constraints dialog box, click in the Expression field and then click the ellipses (...).

  3. “CHECK 约束表达式” 对话框中,键入 CHECK 约束的 SQL 表达式。In the Check Constraint Expression dialog box, type the SQL expressions for the check constraint. 例如,若要将 SellEndDate 表的 Product 列中的条目限制为大于等于 SellStartDate 列中的日期的值,或者为 NULL 值,则键入:For example, to limit the entries in the SellEndDate column of the Product table to a value that is either greater than or equal to the date in the SellStartDate column or is a NULL value, type:

    SellEndDate >= SellStartDate OR SellEndDate IS NULL  
    

    或者,如果要求 zip 列中的项为 5 位数,请键入:Or, to require entries in the zip column to be 5 digits, type:

    zip LIKE '[0-9][0-9][0-9][0-9][0-9]'  
    

    备注

    确保将任何非数字约束值包含在单引号 (') 中。Make sure to enclose any non-numeric constraint values in single quotation marks (').

  4. 单击“确定”。Click OK.

  5. 在“标识”类别中,您可以更改 CHECK 约束的名称并且为该约束添加说明(扩展属性)。In the Identity category, you can change the name of the check constraint and add a description (extended property) for the constraint.

  6. “表设计器” 类别中,您可以设置何时强制约束。In the Table Designer category, you can set when the constraint is enforced.

    更改为:To: 在以下字段中选择“是”:Select Yes in the Following Fields:
    对在创建约束前存在的数据测试约束Test the constraint on data that existed before you created the constraint 在创建或启用时检查现有数据Check Existing Data On Creation Or Enabling
    在此表上发生复制操作时强制约束Enforce the constraint whenever a replication operation occurs on this table 强制用于复制Enforce For Replication
    在此表中插入或更新行时强制约束Enforce the constraint whenever a row of this table is inserted or updated 强制用于 INSERT 和 UPDATEEnforce For INSERTs And UPDATEs
  7. 单击“关闭”。Click Close.

使用 Transact-SQLUsing Transact-SQL

创建新的 CHECK 约束To create a new check constraint

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute.

    ALTER TABLE dbo.DocExc   
       ADD ColumnD int NULL   
       CONSTRAINT CHK_ColumnD_DocExc   
       CHECK (ColumnD > 10 AND ColumnD < 50);  
    GO  
    -- Adding values that will pass the check constraint  
    INSERT INTO dbo.DocExc (ColumnD) VALUES (49);  
    GO  
    -- Adding values that will fail the check constraint  
    INSERT INTO dbo.DocExc (ColumnD) VALUES (55);  
    GO  
    
    

有关详细信息,请参阅 ALTER TABLE (Transact-SQL)For more information, see ALTER TABLE (Transact-SQL).