SQL 2005+

SQL Server Check Constraints

The twenty-fourth part of the SQL Server Programming Fundamentals tutorial describes check constraints. These constraints add business rules to database tables to prevent invalid information being stored and damaging the domain integrity of the database.

What is a Check Constraint?

We have already mentioned the concept of database constraints in this tutorial. Constraints in general apply rules to a database to prevent invalid information from being stored. There are various types of constraint including primary keys, foreign keys and unique constraints. A check constraint applies a business rule to a column or table. It checks information as new rows are inserted or when existing rows are updated. If the constraint's rule is broken because the data is invalid, the update fails.

A check constraint includes a conditional expression, or predicate, that returns a Boolean value. When an insert or update is attempted, the expression is evaluated. If the resultant value is true, the change is permitted. If false, an error occurs. As an example, consider a table that contains pricing information for contract renewals. The business that owns the database may allow a free-of-charge contract but never a negative price. The constraint in this case would include the predicate, "ContractValue >= 0". A negative contract value would fail this constraint and be disallowed.

Some developers advise against the use of check constraints, arguing that the business logic of an application should be retained within the business logic layer of an application. However, in such a scenario, it becomes possible for invalid data to be added to the database. This can happen because there are bugs in the business logic or because data is updated via a different route. For example, when a database is used by multiple applications with differing logic. A check constraint prevents this problem.

Limitations

There are several limitations to consider when creating check constraints. The first of these is that Boolean expressions in SQL Server can produce three results, not two. These are true, false and unknown. The unknown value will occur, for example, when comparing NULL values. If a constraint check returns unknown, the insert or update will be permitted. You should carefully consider this situation when creating constraints, especially those with complex predicates that include a combination of checks.

There can be problems with constraints that include data type conversion. You should avoid this type of constraint wherever possible. Note that this includes implicit data type conversion.

If you include a user-defined function within a check constraint, you can perform checks that use values from other tables or from other rows in the same table. For example, you could create a function that returns the number of rows in the table and include this in a check constraint that specifies that there must be at least one row in the table. However, this check constraint will not be tested when deleting rows from the table so will not provide the desired effect. Such a business rule must be enforced using a database trigger instead.

Creating Check Constraints

In this instalment of the tutorial we will create check constraints against the tables in the JoBS tutorial database. This database has been created, modified and populated throughout the course of these articles. If you do not have an up-to-date copy of the database, you can create one by downloading and running the script from the link at the top of this page.

Creating Check Constraints Using SQL Server Management Studio

Check constraints can be created using the tools within SQL Server Management Studio (SSMS). We will use the Check Constraints dialog box, pictured below, to create our first check constraint. The predicate will prevent contracts from being created with a negative contract value.

To begin, locate the Contracts table in the Object Explorer. Right-click the name of the table and choose "Design" from the context-sensitive menu that appears. The table designer will be displayed. To add a constraint, right-click any column in the table designer and select "Check Constraints..." from the menu. Alternatively, choose this option from the Table Designer menu or click the Manage Check Constraints toolbar button. The Check Constraints dialog box will be displayed.

There are several options that may be completed in the dialog box:

• Expression. This option is the most important. This is where you must create the predicate that will be enforced by the constraint. To enforce that the contract value must always be zero or greater, enter "ContractValue>=0" into the field.
• Name. As with the other constraints that we have created, check constraints should be given a unique name. Conventionally this will have the prefix "CK_". For the new constraint, use the name "CK_Contracts_ValueIsPositive".
• Description. If you wish, you can describe the purpose of the constraint in the Description field.
• Check Existing Data on Creation or Re-Enabling. When a check constraint is first created there may already be data in the table that does not fulfil the rules being enforced. Similarly, if a constraint is disabled the data may have become invalid before it is re-enabled. If this option is set to Yes and data that breaks the rule exists, it will not be possible to create or re-enable the constraint. If the value is set to No, the constraint will be created but the invalid data will remain in the table. The value you choose will depend upon the situation. In this case, leave the default value of Yes.
• Enforce For Inserts And Updates. When set to No, the check constraint is disabled and the creation of invalid data is permitted. This is useful when you wish to temporarily disable a constraint. Set the value to Yes.
• Enforce For Replication. When you have more than one database and the table is replicated, changes made in one copy of the table are duplicated in the other. As the check constraint should have been checked and passed when the modification was initially made, you may not want to repeat the check when an update is made by the replication engine. Set this option to No.

To create the new check constraint, accept the new changes and close the dialog box. Close the table designer and save the changes. The check constraint will now appear in the Constraints section of the table's branch of the Object Explorer tree. If it doesn't, refresh the constraints list.

We can test the constraint by attempting to insert invalid data into the Contracts table. Execute the following Transact-SQL (T-SQL) statement, which tries to create a negative contract value. You should receive an error and the table will remain unmodified.

INSERT INTO Contracts
    (CustomerAddressId, ContractValue)
VALUES
    (21, -1)

Creating Check Constraints using Transact-SQL

When you are creating a new table, you can include check constraints in the CREATE TABLE command. There are several variations upon the syntax and positioning of the check constraint that can be used, depending upon the results that you require. The syntax that we will use for the check constraint itself is:

This syntax allows us to name the constraint and to provide the predicate. It is possible to omit the first two parts of this syntax, leaving only the CHECK keyword and the expression. However, if you do so SQL Server will automatically generate a name for the constraint, which will make future maintenance more difficult.

Check constraints can be defined at the column level. In this case, the constraint is added to the end of the column definition in the CREATE TABLE command. The expression in a column-level check constraint may not reference any other column in the table. In the case of the previously defined constraint this is acceptable, so the table could have been created using the following T-SQL statement:

CREATE TABLE Contracts
(
    ContractNumber INT IDENTITY NOT NULL,
    CustomerAddressId INT NOT NULL,
    RenewalDate SMALLDATETIME,
    RenewAutomatically BIT,
    ContractValue MONEY NOT NULL
        CONSTRAINT CK_Contracts_ValueIsPositive CHECK (ContractValue >= 0),
    DaysUntilRenewal AS datediff(d,getdate(),RenewalDate)
    CONSTRAINT PK_Contracts PRIMARY KEY CLUSTERED 
    (
        ContractNumber
    )
)

You may also connect a check constraint to an entire table, rather than a single column. When creating a table-level constraint, the expression can include references to any column, allowing comparison of several column values as required. To create this type of constraint, it is added to the list of columns and constraints for the table, separated from other elements with a comma. We could define the Contracts table using a table-level constraint as follows:

CREATE TABLE Contracts
(
    ContractNumber INT IDENTITY NOT NULL,
    CustomerAddressId INT NOT NULL,
    RenewalDate SMALLDATETIME,
    RenewAutomatically BIT,
    ContractValue MONEY NOT NULL,
    DaysUntilRenewal AS datediff(d,getdate(),RenewalDate)
    CONSTRAINT PK_Contracts PRIMARY KEY CLUSTERED 
    (
        ContractNumber
    ),
    CONSTRAINT CK_Contracts_ValueIsPositive CHECK (ContractValue >= 0)
)

Adding a Check Constraint to an Existing Table

Often you will want to add a constraint to an existing table. As with other constraint types, this can be achieved using the ALTER TABLE command's ADD clause.

We can use this method to add a new constraint to the Engineers table. In this case, we will ensure valid input of the engineer's charges by adding a check that the hourly rate is less than or equal to the overtime rate in all cases.

ALTER TABLE Engineers
ADD CONSTRAINT CK_Engineers_OvertimeRateValid
CHECK (HourlyRate <= OvertimeRate)

Try executing the following INSERT statement. The check constraint should prevent this change from being accepted.

INSERT INTO Engineers
    (EngineerName, HourlyRate, OvertimeRate)
VALUES
    ('Bob Smith', 20, 19.50)

Ignoring Existing Invalid Data

When creating constraints, it is possible that data already exists that does not meet the criteria enforced by the constraint's predicate. If this is the case, the creation of the check constraint will fail. As mentioned previously, it is possible to add a constraint without checking the existing data in the table. In this case, any data that does not pass the constraint's checks will remain in the table but new inserts and updates will be restricted as expected. This allows you to create the constraint and request that the database's users repair the invalid information at a a later time.

To create a constraint and ignore invalid information, add the WITH NOCHECK clause to the ALTER TABLE command as follows:

ALTER TABLE Engineers WITH NOCHECK
ADD CONSTRAINT CK_Engineers_OvertimeRateValid
CHECK (HourlyRate <= OvertimeRate)

Creating a Check Constraint that is Ignored During Replication

As with creating a check constraint using the SSMS graphical user interface, you can generate constraints using T-SQL that are not tested against information that is created or modified during replication. To do so, the "NOT FOR REPLICATION" clause is appended to the CHECK keyword:

ALTER TABLE Engineers 
ADD CONSTRAINT CK_Engineers_OvertimeRateValid
CHECK NOT FOR REPLICATION (HourlyRate <= OvertimeRate)

Dropping Check Constraints

If you no longer require a check constraint because the business rules governing the database have changed, you can drop it using the standard syntax for removing a constraint. You should only drop a constraint if it will definitely not be needed again. Otherwise, you may wish to consider simply disabling it. To drop the constraint on the Engineers table, you could use the following T-SQL:

Disabling Check Constraints

There are occasions when you may want to temporarily disable a check constraint. This is simple to achieve with the ALTER TABLE command and the NOCHECK clause. In the example below, the CK_Engineers_OvertimeRateValid check constraint is disabled:

Re-Enabling a Disabled Constraint

When you have completed the process that required the constraint to be disabled, you can re-enable it using the CHECK clause instead of NOCHECK:

By default, when you re-enable a check constraint, the data in the table is not retested. Any information that breaks the business rule remains unchanged. Sometimes you will prefer that the table's data is rechecked and that the check constraint is not re-enabled if invalid information exists. If this is the case, add the WITH CHECK clause to the ALTER TABLE command, as follows:

ALTER TABLE Engineers WITH CHECK
CHECK CONSTRAINT CK_Engineers_OvertimeRateValid