CHECK CONSTRAINT in MS SQL - The Rake We Walked On

  • Tutorial

This article will be about how one friendly team of web developers, without an experienced SQL developer, added Check Constraint to the table and walked through a few simple but not immediately obvious rakes. The features of the T-SQL syntax, as well as the nuances of the work of constraints (CONSTRAINTs), without knowing which, you can spend a lot of time trying to understand why something is not working, will be analyzed. The feature of SSDT operation will also be affected , namely how the migration script is generated, if necessary, add or change restrictions (CONSTRAINTs).

In order for the reader to quickly understand whether it is worth reading the article or not, I will first consider an abstract problem, in the process of solving which the questions “Why so?” Will be asked. If you immediately know the answer, feel free to quit reading and continue to the next article.



Develop a harem?


"Harem" - a system that will allow you to keep records of people in the "temple of love."
For ease of development, we accept the following:
  • guests in the harem are prohibited and, accordingly, are not stored in the database, that is, only the "owners" and their wives are stored
  • the wives and their "master" have the same name
  • each harem can be uniquely identified by surname, i.e. the same surname cannot be found in different harems.

The Persons table is created for storing people:


At the last moment, it comes to an insight that at the level of the base scheme we do not guarantee the existence of only one man in the harem. We decide to fix this by adding a check constraint:


based on a scalar-valued Function:


“Why so?” No. 1.


When trying to insert absolutely valid data (both women and men), we understand that we broke everything. Insert fails with the following error:


“Why so?” No. 2.


After we have overcome the problem of data insertion, we decide to embed ourselves in the QA environment. We create a migration script using SSDT, quickly look through it. We find some not very clear line (highlighted in red).


From the comment in the PRINT statement, it seems that this is starting a constraint check on existing lines. But when creating the constraint, we indicated that you do not need to check existing rows ( “Check Existing Data On Creation Or Re-Enabling” was set to “No” ). Therefore, we begin to google and find a “useful” post. After reading the answer and all the comments on it, we gain deep confidence that this instruction includes checking when inserting new lines, and does not validate existing ones, that is, we must leave this line, otherwise the restriction will never be checked at all.
With pride in the work done, we send the script, we wait ... After X hours, a report arrives that our migration script successfully failed. We look at the report.



We understand that it was the suspicious instruction that failed the migration.

“Why so?” No. 1 - Explanation.


Everything is extremely simple here. We forgot that the CHECK CONSTRAINT conditions are checked already after inserting the row into the table and at the moment the first man is inserted into the harem, the correct condition would be equality to one, not zero. As a result, the function was rewritten much easier.


Computed column
Computed columns can be used in the constraint expression, but only if they are physically stored, i.e. they have the IsPersited property set to Yes. At the stage of checking, all calculated columns will have the correct values, and if you update the values ​​on which the calculated value depends, already converted values ​​will be transferred in the CHECK CONSTRAINT expression.

Justification
In order to somehow justify such inattention, I will say that in our case the conditions for checking the restrictions were much more ornate, and not all were related to the number of rows in the updated table. And the data was inserted into the table only as a result of running a complex script, which was extremely difficult to deal with.


“Why so?” No. 2 - Explanation.


Everything turned out to be not so transparent here. First, I still had to figure out the true purpose of the fallen instruction. And, to our great surprise, we realized that she was doing exactly what was said in the comment, and not what was described in the found “useful” post (the analysis of the syntax will be below).
Having learned this, it was logical to assume that when creating the migration script, a base was selected in which on CK_Persons the value “Check Existing Data On Creation Or Re-Enabling” was “Yes”, and not “No”. But this theory failed. By changing this value and generating a new script, it became clear that SSDTs generally ignore this value. We started to sin for a bug in SSDT.
The next stage of the search led us to the next post , from which we already realized that this was a "feature, not a bug."
According to the design of SSDT, when creating a script, a restriction is always created, which is included, i.e. checked for all future INSERT / UPDATE. The first ALTER instruction in our migration script is responsible for this.
The second ALTER instruction (highlighted in red) is responsible for validating existing data and is optional. Whether this instruction will be added to the migration script is subject to a special script generation option:


By enabling it, for each new migration script, we activate the validation of existing data, i.e. an optional instruction will be inserted into it (second ALTER). Otherwise, the instruction is simply missing and validation is not performed on existing data. It is not regrettable, but SSDT generates a migration script on the principle of all or nothing. You can either enable validation for existing data for all newly added restrictions, or skip it for all. To fine-tune the behavior you have to edit the script manually.

Constraints in MS SQL


As mentioned above, in this article it remains to deal with the tricks of the syntax for creating and updating test constraints. But before we get started, let's take a look at some general information about restrictions in MS SQL Server for completeness.
Limitations - a mechanism that allows you to set a set of rules aimed at maintaining data integrity. Rules can be set both at the column level in the table, and at the level of the entire table.
MS SQL Server supports the following types of restrictions:
  • NULL / NOT NULL restriction - is set at the level of some column and determines whether the NULL value can be stored in the column.
  • UNIQUE constraint - allows you to ensure the uniqueness of values ​​in one or more columns.
  • A PRIMARY KEY constraint is almost the same as a UNIQUE constraint, but unlike it, a PRIMARY KEY does not allow you to store NULL.
  • CHECK restriction - allows you to set some logical condition, which must be true (TRUE) when inserting or updating data in the table. It can be set both at the level of one column and at the table level.
  • FOREIGN KEY constraint - allows you to provide referential connectivity of two tables. When you insert a value into a column (or columns) with a FOREIGN KEY constraint, it will check for the same value in the table that FOREIGN KEY points to. If there is no value, then updating or inserting a row fails. Only an NULL value can be an exception if a NOT NULL constraint is not specified on a column. In addition, you can only refer to a column with unique values, i.e. with UNIQUE or PRIMARY KEY constraint. You can also set the behavior, in case of updating or deleting a row, in the "father" table:
    • NO ACTION - it is forbidden to change the father's table
    • CASCADE - subordinate rows will be updated or deleted, depending on the action performed on the father’s table
    • SET NULL - the value in the subordinate table will be set to NULL
    • SET DEFAULT - the value in the subordinate table will be set to the default value.


Now a little more about CHECK CONSTRAINT'ah. Consider the limitation mentioned above. The following is the properties window for this restriction in Management Studio:


The main properties are:
  • Expression - any valid T-SQL expression in which you can refer to the values ​​in the checked row by column name
  • Name - a name that uniquely identifies the constraint within the database
  • Check Existing Data On Creation Or Re-Enabling - if the restriction is created on an existing table, then this value “No” allows not to skip the validation of existing rows; since the existing check can be temporarily disabled, this property also determines whether validation of existing rows will be performed when the restriction is enabled.
  • Enforce For INSERTs And UPDATEs - enable (Yes) or disable (No) restriction
  • Enforce For Replication - allows you to skip validation when inserting or updating rows by the replication agent

All this information is also available to us from the sys.check_constraints system view . It contains one row for each CHECK CONSTRAINT in the database. We sometimes use it in migration scripts when we need to make sure of the existence or absence of any restriction.

Sys.check_constraints examples


Sql
DECLARE @name NVARCHAR(128) = 'CK_Persons'
SELECT	CASE [is_not_trusted] WHEN 1 THEN 'No' ELSE 'Yes' END AS [Check Existing Data],
		CASE [is_disabled] WHEN 1 THEN 'No' ELSE 'Yes' END AS [Enabled],
		CASE [is_not_for_replication] WHEN 1 THEN 'NO' ELSE 'YES' END AS [Enforce For Replication]		
FROM [sys].[check_constraints]
WHERE name = @name


You can get the answer in a more familiar format using the UNPIVOT operator:

Sql
DECLARE @name NVARCHAR(128) = 'CK_Persons'
SELECT [Properties], [Values]
FROM (SELECT CAST([definition] AS VARCHAR(MAX)) AS [Expression],
			 CAST(CASE [is_not_trusted] WHEN 1 THEN 'No' ELSE 'Yes' END AS VARCHAR(MAX)) AS [Check Existing Data On Creation Or Re-Enabling],
			 CAST(CASE [is_disabled] WHEN 1 THEN 'No' ELSE 'Yes' END AS VARCHAR(MAX)) AS [Enforce For INSERTs And UPDATEs],
		     CAST(CASE [is_not_for_replication] WHEN 1 THEN 'NO' ELSE 'YES' END AS VARCHAR(MAX)) AS [Enforce For Replication],
		     CAST([create_date] AS VARCHAR(MAX)) as [Created],
		     CAST([modify_date] AS VARCHAR(MAX)) as [Modified]
	 FROM [sys].[check_constraints]
	 WHERE name = @name) p
UNPIVOT
(
	[Values] FOR [Properties] 
	IN (
		[Expression],
		[Check Existing Data On Creation Or Re-Enabling] ,
		[Enforce For INSERTs And UPDATEs],
		[Enforce For Replication],
		[Created],
		[Modified]
		)
) AS unpvt;



Features of the work of CHECK CONSTRAINT:

  • It works only with INSERT and UPDATE operations; when DELETE is executed, the condition is not checked
  • If the test condition is NULL, then it is considered that CHECK CONSTRAINT is not broken


Syntax CHECK CONSTRAINT


A validation constraint has a number of properties that you must set when creating it. Some of them can be set only at creation, and some are available for change, only on the already created restriction. The table below shows these features.
 “... ADD CONSTRAINT ...”
(creation)
“ALTER ... CONSTRAINT ...”
(change)
Name
+
-
Expression
+
-
Check Existing Data On Creation Or Re-Enabling
+
+
Enforce For INSERTs And UPDATEs
-
+
Enforce for replication
+
-


Adding a new CHECK CONSTRAINT


T-SQL Template Syntax Basics
  • In square brackets “[]” - optional constructions are indicated and can be omitted from the final expression
  • In curly brackets "{}" - a list of possible designs is indicated, from which one must be selected
  • Vertical bar "|" - separates elements in curly brackets, among which you need to select a single element




Optional sections:
  1. [WITH {CHECK | NOCHECK}] - if absent, the value WITH CHECK is applied
  2. [NOT FOR REPLICATION] - if the design is specified, then the restriction is not checked when inserting or updating data at the time of replication; if the design is omitted, the constraint is checked.


Note: the template is provided for the case of creating a constraint on an existing table. You can also create a constraint at the time the table is created, then the command will begin with the word CREATE and the description of the table columns will go to the word WITH.

Examples:
Table for examples
Examples of commands will be given for the simplest Employees table, which looks like this:




Modify Existing CHECK CONSTRAINT


The ALTER TABLE construct is used to update an existing validation constraint. Only the following properties are available for change:
  • Check Existing Data On Creation Or Re-Enabling
  • Enforce For INSERTs And UPDATEs



Optional sections:
  1. [WITH {CHECK | NOCHECK}] - if absent, WITH NOCHECK is applied
  2. [, ... n] - allows you to specify the name of more than one restriction to which the changes will be applied; using the word ALL changes apply to all test constraints on the table

Note 1 : although the name cannot be renamed using the ALTER TABLE syntax, it is still possible to do this using the sp_rename system stored procedure.
Note 2 : if you need to change the Expression or Enforce For Replication properties, you must first delete the existing constraint, and then recreate it with the desired values ​​for these properties.

Examples:


Undocumented behavior


There are a number of cases where the execution of commands leads to unexpected results. And I could not find an explanation on the msdn website.
To see this, you need to consider all possible combinations of states in combination with all possible options for commands. Then it will be seen that in 5 cases the obtained value of the “Check Existing Data” property does not meet expectations.

Status before command executionT-SQL commandStatus after command execution
Check existing dataEnforce For INSERTs And UPDATEsCheck existing dataEnforce For INSERTs And UPDATEs
NoNoNOCHECKNoNo
NoYesNOCHECKNoNo
YesYesNOCHECKNoNo
NoNoCHECKNoYes
NoYesCHECKNoYes
YesYesCHECKYes *Yes
NoNoWITH NOCHECK NOCHECKNoNo
NoYesWITH NOCHECK NOCHECKNoNo
YesYesWITH NOCHECK NOCHECKNoNo
NoNoWITH NOCHECK CHECKNoYes
NoYesWITH NOCHECK CHECKNoYes
YesYesWITH NOCHECK CHECKYes *Yes
NoNoWITH CHECK NOCHECKNo **No
NoYesWITH CHECK NOCHECKNo **No
YesYesWITH CHECK NOCHECKNo **No
NoNoWITH CHECK CHECKYesYes
NoYesWITH CHECK CHECKYesYes
YesYesWITH CHECK CHECKYesYes

(*) The value of the “Check Existing Data” property can be converted from the value “Yes” to the value “No” only if the current value of the “Enforce For INSERTs And UPDATEs” property is different from that specified in the command.

(**) “Check Existing Data” can be “Yes” only if the restriction is enabled (Enforce For INSERTs And UPDATEs = “Yes”). That is, in the WITH CHECK NOCHECK command, the WITH CHECK part will be ignored and the “Check Existing Data” will not be set to “Yes”. This also explains why there are only 3 options for each team (not 4) as initial states.

Delete existing CHECK CONSTRAINT


The team is very simple and requires no further explanation. Another template:


Conclusion


I sincerely hope that after reading this article, you will not walk the rake that has accumulated a couple of unpleasant cones for us. And also you can comfortably create and maintain migration scripts that have logic for working with CHECK CONSTRAINT. Good luck

Also popular now: