Edit

Create check constraints

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

You can create a check constraint in a table to specify the data values that are acceptable in one or more columns in SQL Server by using SQL Server Management Studio or Transact-SQL. For more information on adding column constraints, see ALTER TABLE column_constraint.

For more information, see Unique constraints and check constraints.

Remarks

To query existing check constraints, use the sys.check_constraints system catalog view.

Permissions

Requires ALTER permissions on the table.

Use SQL Server Management Studio

  1. In Object Explorer, expand the table to which you want to add a check constraint, right-click Constraints and select New Constraint.

  2. In the Check Constraints dialog box, select in the Expression field and then select the ellipses (...).

  3. In the Check Constraint Expression dialog box, type the SQL expressions for the check constraint. 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, to require entries in the zip column to be five digits, type:

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

    Note

    Make sure to enclose any non-numeric constraint values in single quotation marks (').

  4. Select OK.

  5. 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.

    Action Select Yes for the following options
    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 Enforce For INSERTs And UPDATEs

  7. Select Close.

Use Transact-SQL

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    First, create the constraint.

    ALTER TABLE dbo.DocExc
ADD ColumnD INT NULL CONSTRAINT CHK_ColumnD_DocExc CHECK (
    ColumnD > 10
    AND ColumnD < 50
);
GO

    To test the constraint, first add values that pass the check constraint.

    INSERT INTO dbo.DocExc (ColumnD) VALUES (49);

    Next, attempt to add values that fail the check constraint.

    INSERT INTO dbo.DocExc (ColumnD) VALUES (55);

Related content