Some details about constraints (constraint) in SQL Server

The source of this article: http://www.cnblogs.com/wy123/p/7350265.html
(It is not the original works right to retain the source, I my book still far to reach, just to link to the original text, because the following may exist some errors to amend or supplement, without him)

Constraints in SQL Server databases (constrint) are intended to ensure the integrity and consistency of the database, when a table can be built to specify a field to conform to certain constraints, such as uniqueness (or primary key) constraints, non-null constraints, default value constraints, etc.
For the specific constraints, can be divided into primary key (unique key) constraints, default value constraints, check constraints, foreign key constraints and other categories.

How constraints are Created

1, specify when building the table

As below, you can specify certain fields to satisfy certain constraints when building a table.

2, specifying how constraints are Created

That is, no constraints are specified when the table is being built, and the constraints on some fields are specified in the form of ALTER TABLE after the table is constructed

　　　　

For a check or default constraint, you can also define the rule in advance, and then bind the rule to the field of the corresponding table
The advantage of having a check constraint and a default value constraint defined below, and then binding the table's fields to the corresponding constraints, is that for some complex constraints, the constraints can be reused after they are defined.
It is important to note that for rule, the null value does not conflict with any rule.
For example, as follows, although the Testconstrint.sex field is specified by the rule rule_sex, if the SEX definition is int, the field can only be 0 or 1 when writing the data, writing 2 is a failure,
However, it is possible to write a null value to this field because NULL does not have any meaning (or no result) compared to the (0,1) specified by the rule

　　　　

Some characteristics of constraints

For unnamed constraints that are specified when the table is built, SQL Server automatically generates the default constraint name with the suffix random, in order to increase the normalization, it is normally not allowed to write this, because we do not want to see a heap of seven or eight dirty names in the database

　　　　

If you specify the name of the constraint, it looks more canonical.

If this is done by defining a rule and then binding to a table, through SSMs's graphical interface, the content after the constraint expansion is not visible (although the constraint is in effect)

The pits that are not easily noticed in database constraints

The constraint looks very simple, whether it is directly specified in the construction of the table, or by the ALTER TABLE to increase the constraints, there seems to be no problem, all the way to Rome, but there are still some small pits.
A constraint in a database does not allow duplicate names.
This means that there cannot be a situation with the same name between different tables, or between tables and custom constraints, including primary KEY constraints (which cannot have the same name), checking constraints (check), default value constraints, etc.
The constraint is the same as the table, a schema in a library cannot define a table of the same name, a schema in a library cannot define a constraint with the same name.
Now it's not hard to understand why SQL Server defaults to the name of the constraint generated, followed by a string of random characters?

1, the constraint between table and table cannot have the same name

　　　　

2, the table cannot have the same name as the custom constraint name.

　　　　

Of course, some people will say, this is what, when the definition of the error will know, there is a bigger hole.
Here is another way of defining a constraint, specifying a named PRIMARY KEY constraint during a table-building process.

such as the following script to define the temporary table, there seems to be no problem, and indeed no problem,
In practice, a developer's problem was written, a temporary table was defined in the stored procedure, a named PRIMARY KEY constraint was specified when the temporary table was defined (possibly the way the physical table was copied), the test passed and published into the production environment, and everything looked very normal.
After the release test two, also behaved as normal, until the system is really used by the user, some user feedback system (related to the function of the place) occasionally error, sometimes normal, sometimes not normal (in fact, the most difficult to diagnose this problem).
The monitoring application will find that during a certain period of time, the stored procedure is found to be continuously error-caused by "Unable to create indexes or constraints"
Developers are also very wronged, after the submission of code, it is clear that the test passed, more abominable is, occasionally will error, most of the time is normal.

By observing its code, and then calmly thinking about it, it is not difficult to understand that the constraints in the database cannot have the same name, and of course temporary tables survive the temporary library is no exception,
When the temporary table runs out, it will be destroyed automatically, which means that if all the sessions are executed serially, this is completely fine.
But in the case of concurrent calls, different sessions call this stored procedure at the same time.
Once the stored procedure is called concurrently, the different session will create the same name constraint, only one will succeed, so to see, the single-threaded test is normal, and after the error is not difficult to understand.

　　　　

Summarize:

In order to ensure the integrity and consistency of the database (foreign key, this is not involved), you can use constraints to achieve this purpose, but the constraints themselves have some of their own rules and characteristics, and it is not different from other database objects, do not allow the same schema under the same name of the object.
If not used in accordance with some of its own rules, it is possible to cause some potential problems.
In order to standardize the naming of constraints, when defining the constraint name, strictly follow the way of prefix +schema+tablename+columnname to define, if it is a temporary table, prohibit the definition of naming constraints.

Some details about constraints (constraint) in SQL Server