CHECK CONSTRAINT in MS SQL - Rakes we walked through

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

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


')

Develop harem?

"Harem" - a system that will allow to keep records of people in the "temple of love."
For ease of development, let's take the following:

• guests are prohibited in the harem and, accordingly, are not stored in the database, i.e. only the “owners” and their wives are kept
• wives and their “master” surname match
• by surname, you can uniquely identify each harem, that is, the same surname in different harems cannot meet.

For storing people, a Persons table is created:


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


based on scalar user-defined function (scalar-valued Function):

“Why so?” №1.

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

“Why so?” №2.

After we have overcome the problem of data insertion, we decide to go into the QA environment. Create a migration script using SSDT, quickly review it. We find some not very clear line (highlighted with a red frame).


From the comment in the PRINT instruction, it seems that this is the start of checking the restriction on already existing strings. But when creating the restriction, we indicated that the existing strings do not need to be checked ( “Check Existing Data On Creation Or Re-Enabling” was set to “No” ). Therefore, we start to google and find a "useful" post . After reading the answer and all the comments to it, we gain a deep confidence that this instruction includes checking when inserting new lines, rather than validating existing ones, that is, we definitely need to leave this line, otherwise the restriction will never be checked at all.
With pride for the work done, we are sending a script, waiting ... After X hours, a report arrives that our migration script has failed successfully. We look at the report.



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

“Why so?” №1 - Explanation.

It's all very simple. We have forgotten that the checking of the conditions of the CHECK CONSTRAINT occurs after inserting the row into the table and at the time of inserting the first man into the harem, the equality of one, and not zero, is the right condition. As a result, the function was rewritten much easier.

“Why so?” No. 2 - Explanation.

Here everything was not so transparent. First, I had to understand the true purpose of the fallen instruction. And, to our great surprise, we realized that she does exactly what is said in the commentary, and not what is described in the found “useful” post (the syntax will be analyzed 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 of “Check Existing Data On Creation Or Reabling” was “Yes”, not “No”. But this theory has failed. Changing this value and generating a new script, it became clear that SSDT, generally ignore this value. Began to sin on the presence of a bug in the SSDT.
The next stage of the search brought us to the next post , from which we already understood that this is "a feature, not a bug."
According to the design of SSDT, when creating a script, a constraint is always created, which is enabled, 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 answered by a special script generation option:


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

Constraints in MS SQL

As mentioned above, this article is left to deal with the wisdom of the syntax for creating and updating verification constraints. But before we start, let's recall for the sake of completeness a bit of general information about the limitations in MS SQL Server.
Restrictions - 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 a column and determines whether a NULL value can be stored in a column.
• UNIQUE constraint - allows you to ensure the uniqueness of values ​​in one or more columns.
• PRIMARY KEY constraint is practically the same as the UNIQUE constraint, but unlike it, PRIMARY KEY does not allow storing NULL.
• CHECK restriction - allows you to specify some logical condition that must be true (TRUE) when inserting or updating data in the table. It can be set both at the level of a single column and at the level of a table.
• 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 be checked to see if the same value in the table that FOREIGN KEY points to. If there is no value, the update or insertion of the line fails. An exception can only be a NULL value, if the column is not set to NOT NULL. In addition, you can only refer to a column with unique values, i.e. with UNIQUE or PRIMARY KEY restriction. 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 table
  • CASCADE - subordinate rows will be updated or deleted, depending on what is performed by the action 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. Consider the constraint that was mentioned above. Below is the properties window of 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 is a name that uniquely identifies the constraint within the database.
• Check Existing Data On Creation Or Re-Enabling - if a restriction is created on an already existing table, then this value “No” allows not to miss the validation of existing rows; Since the existing check can be temporarily turned off, this property also determines whether the existing strings will be validated when the limit is enabled.
• Enforce For INSERTs And UPDATEs - enables (Yes) or disables (No) restriction
• Enforce For Replication - allows you to skip checking when a replication agent inserts or updates strings

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

Sys.check_constraints usage examples

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

Features of CHECK CONSTRAINT:

• It works only for INSERT and UPDATE operations; if DELETE is performed, the condition is not checked
• If the test condition is NULL, then it is considered that the CHECK CONSTRAINT is not violated.

Syntax CHECK CONSTRAINT

The check constraint has a number of properties that must be set at creation. Some of them can be set only at creation, and some are available for change, only on the constraint already created. The table below shows these features.

	“… ADD CONSTRAINT ...” (creature)	“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

Optional sections:

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

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

Examples:

Change existing CHECK CONSTRAINT

The ALTER TABLE construct is used to update the existing check constraint. Only the following properties are available for editing:

• Check Existing Data On Creation Or Re-Enabling
• Enforce for INSERTs and UPDATEs

Optional sections:

1. [WITH {CHECK | NOCHECK}] - in case of absence, WITH NOCHECK value is used
2. [, ... n] - allows you to specify the name of more than one constraint to which the changes will be applied; the use of the word ALL changes applies 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 properties of "Expression" or "Enforce For Replication", you must first remove the existing constraint, and then re-create it with the necessary values ​​of 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 site msdn.
In order to see this, it is necessary to consider all possible combinations of states in combination with all possible variants of commands. Then it will be seen that in 5 cases, the resulting value of the “Check Existing Data” property does not meet expectations.

State before command execution		T-SQL command	Status after command execution
Check Existing Data	Enforce for INSERTs and UPDATEs		Check Existing Data	Enforce for INSERTs and UPDATEs
No	No	NOCHECK	No	No
No	Yes	NOCHECK	No	No
Yes	Yes	NOCHECK	No	No
No	No	CHECK	No	Yes
No	Yes	CHECK	No	Yes
Yes	Yes	CHECK	Yes *	Yes
No	No	WITH NOCHECK NOCHECK	No	No
No	Yes	WITH NOCHECK NOCHECK	No	No
Yes	Yes	WITH NOCHECK NOCHECK	No	No
No	No	WITH NOCHECK CHECK	No	Yes
No	Yes	WITH NOCHECK CHECK	No	Yes
Yes	Yes	WITH NOCHECK CHECK	Yes *	Yes
No	No	WITH CHECK NOCHECK	No **	No
No	Yes	WITH CHECK NOCHECK	No **	No
Yes	Yes	WITH CHECK NOCHECK	No **	No
No	No	WITH CHECK CHECK	Yes	Yes
No	Yes	WITH CHECK CHECK	Yes	Yes
Yes	Yes	WITH CHECK CHECK	Yes	Yes

(*) The value of the “Check Existing Data” property can be translated from “Yes” to “No”, only if the current value of the “Enforce For INSERTs And UPDATEs” property differs from the one 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 “Check Existing Data” will not be set to “Yes”. It also explains why there are only 3 options for each command as the initial states (and not 4).

Deleting an existing CHECK CONSTRAINT

The command is very simple and does not require additional explanations. Another pattern:

Conclusion

I sincerely hope that after reading this article, you will not go over the rake, stuffed us a couple of unpleasant cones. And also you can comfortably create and maintain migration scripts, in which there is logic to work with CHECK CONSTRAINT. Good luck!