SQL 2005+

SQL Server Check Constraints (2)

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.

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)