SQL Server constraints

SQL Server-constraint one, the classification of constraints

In SQL Server, there are 3 different kinds of constraints.

1. Entity constraints

Entity constraints are about rows, such as when a row appears with values that are not allowed to appear on other rows, such as a primary key.

2. Domain constraints

Domain constraints are about columns, and for all rows, a column has those constraints, such as a check constraint.

3. Referential integrity constraints

If a column's value must match the value of another column, it means that a referential integrity constraint, such as a foreign key, is required.

Second, the constraint named

Before learning constraints, first understand what to look for when naming constraints.

SQL Server automatically creates names when we do not provide names, but names that are automatically created by the system are not particularly useful.

For example, the system-generated primary key name might be this: pk_employees_145c0a3f.

The PK represents the primary key (primary key), employees is represented in the Employees table, and the remaining "145c0a3f" part is a randomly generated value to ensure uniqueness. This value is only available through script creation, and if you create a table from Managerment Studio, it is pk_employees directly.

For the system automatically generated check constraint names such as: ck_customers_22aa2996. CK represents this is a check constraint, customers represents the Customers table, followed by a 22aa2996 or a random number. If there are multiple check constraints in a table, the naming might look like this:

ck_customers_22aa2996

ck_customers_25869641

ck_customers_267aba7a

If you need to modify one of these constraints, it's hard to tell which of these constraints are.

So, to be able to see what this constraint is for at a glance, we should use a simple and straightforward phrase to name it.

For example, to ensure that a column phone number is properly formatted, we can use a phrase named Ck_customers_phoneno to name it.

In short, the naming to do the following points:

1. Consistency

2. Easy to understand

3, to meet the above two conditions under the circumstances of the simplified name.

Third, key constraints 1, PRIMARY KEY constraint

A primary key is a unique identifier for each row, which can be positioned exactly to a single row, where the primary key column cannot have duplicates in the entire table and must contain a unique value (cannot be null). Because of the importance of the primary key in the relational database, it is the most important of all keys and constraints.

Here's how the primary key is created

　　1. Create a PRIMARY KEY constraint when creating a table.

CREATE TABLE Customer (    customerId        int identity not    null        primary KEY,--Creates a primary KEY constraint    CustomerName    nvarchar (+) not    null);

How, very simple!

　　2. Create a PRIMARY KEY constraint on a table that already exists

Now suppose a table already exists, but there is no primary KEY constraint:

ALTER TABLE    person add constraint pk_employee_id--FOREIGN key name    primary KEY (personId)--personid field name

The ALTER name tells SQL Server the following information:

1, add some content to the table (you can also delete some content in the table)

2. What content is added (a constraint)

3. Naming constraints (allowing direct access to constraints later)

4. Type of constraint (PRIMARY KEY constraint)

5. Which column the constraint applies to.

　　3. Creation of composite primary keys

If you are really management Studio, create a composite primary key, just hold down the CTRL key, select two columns, then set the primary key is OK, very simple. Here's how to create a composite primary key using T-sql:

In many-to-many connections, there is often a table describing the relationship of the other two tables, with the reader and the book as an example:

2, FOREIGN KEY constraints

Foreign keys both ensure data integrity and can represent relationships between tables. After a foreign key has been added, the record that inserted the reference table must either be matched by a record of the referenced column in the referenced table, or the value of the foreign key column must be set to NULL.

Foreign keys and primary keys are different, and the number of foreign keys in each table does not limit uniqueness. In each table, each has a-~253 foreign key. The only restriction is that a column can reference only one foreign key. A column can be referenced by more than one foreign key.

　　1. Create a foreign key when creating a table

CREATE TABLE orders (    orderId        int identity not    null        primary key,    customerId    int. not                NULL        foreign key references customer (CUSTOMERID)-Constraint type-foreign key-reference table (column name));

　　2. Add a foreign key to a table that already exists

Assuming the above code removes the addition of the foreign key row, you can write the following code:

ALTER TABLE orders    add constraint Fk_orders_customerid        --Add constraint name        foreign key (CustomerId)    References Customer (CUSTOMERID)    --FOREIGN KEY constraint, foreign key column name, referenced column name

The constraint you just added takes effect as a previously added constraint, and if a row reference CustomerID does not exist, you are not allowed to add the row to the Orders table.

　　3. Cascade Action

One important difference between foreign keys and other type keys is that the foreign key is bidirectional, that is, not only the value of the restriction child table must exist in the parent table, but also the child rows after each action on the parent table (this avoids orphan). The default behavior of SQL Server is that the "limit" parent row is deleted when a child row exists. However, there are times when any dependent records are automatically deleted, rather than preventing the referenced records from being deleted. Also when you update a record, you may want to rely on records that automatically reference the records that you just updated. In rare cases, you might want to change the reference row to a known state. To do this, you can choose to set the value of the dependent row to null or the default value for that column.

This process of automatic deletion and automatic updating is called cascading. This process, especially the removal process, can go through several layers of relationships (one record relies on another record, and this other record relies on other records). Implementing level linkage in SQL Server requires you to modify the foreign key syntax-just precede the addition with an ON clause. For example:

ALTER TABLE orders    add constraint Fk_orders_customerid        --Add constraint name        foreign key (CustomerId)    references Customer (CUSTOMERID)    --FOREIGN KEY constraint, foreign key column name, referenced column name on        update     no action    --non-cascading update sub-table on        delete     by default modification Cascade      --Cascade delete dependent rows when deleting

When cascading deletes, if one table cascade another table, and the other table cascade the other table, this kind of federation has been down, unrestricted, this is actually a cascade of danger, it is easy to accidentally delete a large amount of data.

The level linkage is made in addition to the no action,cascade, there are set NULL and set default. The latter two are introduced in SQLServer2005, and if you want to be compatible with SQLServer2000, avoid using these two cascading actions. But what they do is very simple: if you perform an update and change the value of a parent row, the value of the child row will be set to NULL, or set to the default value for the column (regardless of SET NULL or set default).

　　4, foreign key other aspects of consideration

There are only possible options in the foreign key:

1. Populate the column with values that match the corresponding columns in the referenced table.

By defining a reference column as not NULL, you can make the foreign key completely mandatory (that is, the user must reference a row of data in the table that must have a match when the data is added).

2, do not populate any values, and make the value null.

When a reference column is allowed to have a null value, the user can choose not to provide a value-even if the referenced table does not have a row that matches the null value, or if it is allowed to be inserted.

3. Unique Constraint

Unique constraints are similar to primary keys in that they all require a unique value on the column (or combination of columns) specified in the table, except that the unique constraint is not treated as a unique identifier for the record in the table (even if you can use it in this way), and you can have multiple unique constraints (only one primary key in each table) )。

Once a unique constraint is established, each value in the specified column must be unique. If you update or insert a record that has an existing value on a column with a unique constraint, SQL Server throws an error and rejects the record.

Unlike the primary key, a unique constraint does not automatically prevent setting a null value, allowing NULL to be determined by the setting of the null option for the corresponding column in the table, but even if null values are true, only one null value can be inserted in a table (if more than one is allowed).

Create a unique constraint on a table that already exists:

ALTER TABLE account    add constraint ak_accountname    --constraint name    unique (account_name)-    -column name

The AK represents the replacement key (Alternate key), and the unique constraint is also called the replacement key.

　　The difference between a primary key and a unique constraint:

• A PRIMARY KEY constraint does not allow null values. Index keys for any index are not allowed to contain null values. But a unique constraint allows a null value to be included, but a unique constraint treats two null values as duplicates, so each column that imposes a unique constraint is allowed to contain only one null value.
• The clustered index is automatically created when the primary key is created, unless the current table already contains a clustered index or the nonclustered keyword is specified when the primary key is created.
• A nonclustered index is automatically created when a unique constraint is created, unless you specify the clustered keyword and the current table does not have a clustered index.
• There can be only one primary key in each table, but more than one unique constraint.

4. Check Constraint

A CHECK constraint constraint can be associated with a column, or it can be associated with a table because they can check the values of one column relative to the value of another column, as long as the columns are in the same table and the values are in the same row as the update or insert. A check constraint can also be used to check whether a combination of column values satisfies a standard.

A check constraint can be defined as a rule with a WHERE clause. An example of a check constraint is as follows:

Goal	Sql
Limit month to the right number	Between 1 and 12
The correct SSN format	Like ' [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] '
Limited to a specific list of courier companies	In (' UPS ', ' Fed Ex ', EMS ')
The price must be positive	UnitPrice >= 0
Referencing another column in the same row	ShipDate >= OrderDate

The list given above is only a small part, and the condition is actually infinitely more. Almost all the conditions that can be put into a WHERE clause can be placed in the constraint. Also, check constraints execute faster than other choices (rules and triggers).

Add a check constraint to the table that already exists:

ALTER TABLE account    add constraint cn_accountage    check     (Account_age >);    --insertion age must be greater than 18

If this view adds an unsatisfied record, the following error will be reported:

Insert into account values (22, ' Hung ', 17)

MSG 547, Level 16, State 0, line 1th The INSERT statement conflicts with the CHECK constraint "Cn_accountage". The conflict occurs in the database "Nx", table "dbo." Account ", column ' Account_age '. Statement has been terminated.

5. Default constraint

As with all constraints, the default constraint is also an integral part of the table definition, which defines what to do when a new row is inserted that does not provide the appropriate data for the column that defines the default constraint. You can define it as a literal value (for example, set a default salary of 0, or set the string as "UNKNOWN"), or a system (GETDATE ()).

For the default constraint, there are a few features to understand:

1. The default value is only used in INSERT statements-ignored in UPDATE statements and DELETE statements.

2. If any value is provided in the INSERT statement, the default value is not used.

3. If no value is provided, the default value is always used.

It is important to note that the rules for the update command are an exception if the display description uses the default value. You can set the value of the update to the default value by using the keyword default.

　　5.1 Define the default constraint when creating a table:

CREATE TABLE person (    person_id int identity NOT NULL        primary key,    person_name nvarchar (+) NOT NULL        Default ' anonymous ',    person_age int not null)

After executing the statement:

Insert into person (person_age) VALUES (24)

A record is inserted in the table as follows:

　　

5.2 Add the default constraint on a table that already exists:

ALTER TABLE person    add constraint cn_defaultname    default    ' anonymous ' for Person_name

6. Disable constraints

Sometimes we want to eliminate constraints temporarily or permanently. However, SQL Server does not provide a way to delete the constraint. SQL Server only allows foreign KEY constraints or check constraints to be disabled while maintaining the integrity of the constraints.

Disabling a data integrity rule is usually because there is already invalid data. Such data is usually divided into the following two categories:

1. Data that was already in the database when the constraint was created

2. Data that you want to add after the constraint is created

SQL Server allows you to disable integrity checking for a period of time to process invalid data for exceptions, and then re-enable integrity (not physically deleting data integrity constraints).

Note: You cannot disable primary KEY constraints or UNIQUE constraints

　　6.1. When creating constraints, ignore data that was not satisfied before checking

To add a constraint, but have not applied to the existing data, you can then execute the ALTER TABLE statement when you add the constraint using the WITH NOCHECK option.

The ALTER TABLE operation will be rejected by SQL Server, following the method above which the check constraint was created, when ALTER TABLE already has an existing non-conforming data in the table itself. SQL Server does not execute a command to create a constraint unless all the data already exists satisfies the criteria for the check constraint. To solve this problem, we can add the with NOCHECK.

We'll start by creating a table with only 3 fields, Id, name, age, and insert a data that doesn't meet the requirements:

Insert into account values (23, ' Hung ', 17)

Then execute the Add constraint command:

ALTER TABLE account    add constraint cn_accountage18    check     (Account_age >);    --insertion age must be greater than 18

SQL Server reported the error:

MSG 547, Level 16, State 0, line 1th the ALTER TABLE statement conflicts with the CHECK constraint "Cn_accountage18". The conflict occurs in the database "Nx", table "dbo." Account ", column ' Account_age '.

This is where we do it in a different way:

ALTER TABLE account with    NOCHECK    add constraint cn_accountage18    check     (Account_age >);    --insertion age must be greater than 18

The above code can be executed successfully, and only the data that is added later will be constrained, and the previously added non-qualifying data record still exists.

6.2 Temporarily disable an existing constraint

When we need to import data from another database into a table, and there are constraints in the table, there may be some data and rule mismatches. Of course, one solution is to delete the constraint, add the required data, and then add it back with Nocheck. But it's too much trouble. We don't have to do this. We can run the ALTER statement with the option named Nocheck, so that the required constraints can be canceled.

Let's take a look at this constraint created in the previous section:

ALTER TABLE account    add constraint cn_accountage18    check     (Account_age >);    --insertion age must be greater than 18

To cancel the above constraint, you can do this:

Alter Table account    NOCHECK    constraint cn_accountage18

Execute command:

Insert into account values (25, ' Cancel constraint ', 17)

Successful execution, a row of data was added successfully.

Note that you can also insert data that does not match the format to the table.

Here to illustrate, do you know if a constraint is enabled or disabled? Sp_helpconstraint command, when we execute sp_helpconstraint, there is a column of status_enabled that shows the enabled state of the constraint:

Sp_helpconstraint Account

　　

Note that the status_enabled listed as disabled description is disabled meaning.

When you want to enable a constraint, you only need to replace the No check in the statement with a check:

Alter Table account    CHECK    constraint cn_accountage18

After execution, the constraint is enabled again: Come sp_helpconstraint to see:

　　

Notice that the status_enabled column becomes enabled.

The two states of the status_enabled are as follows:

Enabled: Enable;

Disabled: Disabled;

7. Rules and default values

Rules and defaults are applied earlier than check and default constraints. They are part of an older SQL Server fallback constraint and certainly not without merit. Since version 7.0, Microsoft has listed rules and defaults for backwards compatibility only, and is not prepared to continue supporting this feature at a later date. Therefore, you should use constraints for generating new code.

The essential difference between a rule, a default value, and a constraint is that a constraint is a feature of a table that does not exist in itself, and that the rules and default values are the actual objects of the table and itself. Constraints are defined in the table definition, and the rules and default values are defined separately and then "bound to" the table.

The independent object attributes of rules and default values allow them to be re-defined without being redefined. In fact, rules and default values are not limited to being bound to a table, they can also be bound to data types.

　　7.1 Rules

Rules and CHECK constraints are very similar. The only difference between them is that the rule can only work on one column at a time. You can bind the same rule to more than one column in a table, but the rules are used for each column, and you don't even realize that the other columns exist. Like qtyshipped

<= qtyordered Such a constraint does not apply to a rule (which refers to multiple columns), whereas a definition like ([0-9][0-9][0-9]) applies to a rule.

　　To define a rule:

The following defines a rule so that you can see the difference first:

CREATE RULE age18rule as    @Age > 18

The comparison here is a variable that, regardless of the value of the column being inspected, will be used to replace the @age. So in this example, the value of any column bound by the rule must be greater than 18.

So far, just a rule has been created, but this rule has not worked on any of the columns of any table, and to activate this rule requires a stored procedure: sp_bindrule.

Bind the rule Age18 to the Person_age column of the table person:

EXEC sp_bindrule ' age18rule ', ' person.person_age ';

At this point, if we perform an insert operation that does not satisfy the rule:

INSERT into person values (' binding rule ', 17)

The following error message will be returned:

MSG 513, Level 16, State 0, insert or update of the 1th column conflict with the rule specified by the previous CREATE rule statement. The statement has been terminated. The conflict occurred in database ' Nx ', table ' Dbo.person ', column ' Person_age '. Statement has been terminated.

It is clear that the rules are in effect.

It is important to note that before binding, the rule has no relation to any table, any column, so when binding, the second parameter is added. Specifies the table name and column name (Tablename.column).

　　Unbind rule:

When we need to unbind a rule on a column, just execute the Sp_unbindrule

Delete the rule that you just bound:

EXEC sp_unbindrule ' person.person_age ';

At this time, do the insert operation just now, there will be no error.

　　To delete a rule:

If you want to remove the rule from the database completely, you can use the very familiar drop syntax in the table.

DROP RULE <rule name>

If you delete the rule that you just created:

DROP RULE Age18rule

　　7.2 Default Value

The default value resembles default. The relationship between the default-default constraint and the rule-check constraint is virtually the same. The difference is in how they are appended to the table and supported by default values ( objects, not constraints ) on the user-defined data type.

The syntax and definition rules for defining default values are similar:

CREATE DEFAULT <default_name> as <default value>

　　To create a default value:

Therefore, suppose you want to define a default value of 0 for age:

CREATE DEFAULT Agedefaultas 0

　　Binding default values:

Similarly, if you do not bind to an object, the default value is not working. To bind, use the stored procedure sp_bindefault.

EXEC sp_bindefault ' Agedefault ', ' person.person_age ';

To resolve bindings for default values from a table, use Sp_unbindefault:

Sp_unbindefault ' Person.person_age ';

　　Delete default values:

If you want to remove a default value from the database completely, you can use the drop syntax, which is the same as the delete rule:

DROP DEFAULT Agedefault

　　7.3 Determine which table and data type use the given rule or default value

If you want to delete or modify a rule or default value. Then you can first see which tables and data types are using them. SQL Server also uses system stored procedures to solve this problem. This stored procedure is sp_depends. Its syntax is as follows:

EXEC sp_depends <object name>

Sp_depends provides a list of all objects that depend on the object you are querying.

8. Trigger

Triggers can also be used to achieve data integrity, which is more of a new article narrative.

9. How to Choose

After the above study, for data integrity, you will find that there are many choices, then how to choose the appropriate constraints?

Limit	Advantages	Disadvantages
Constraints	Fast Other columns can be referenced Occurs before the command executes Follow ANSI Standards	Must be redefined for each table Cannot reference another table Cannot bind to data type
Rules	Stand-alone objects can be reused can be bound to a data type Occurs before the command executes	Slightly slower Cannot use across columns Cannot reference another table Actually only for backwards compatibility
Default value	Very flexible You can reference other columns or other tables can be passed. NET references to other information other than SQL Server	Occurs after the command executes Very expensive system overhead

If you want to implement a more robust logical model and use user-defined data types extensively, the rules and default values are generally used. In this case, the rules and default values can provide many features that are easy to manage without too much programming overhead.

Use a trigger only if you cannot select a constraint. As with constraints, they are appended to the table and must be redefined for each table created. The good thing is that triggers can do almost anything in terms of data integrity. In fact, they are often used as substitutes for foreign keys when there are no foreign keys.

In other cases, the constraint should be chosen as a data integrity solution. They execute quickly and are not difficult to create. Their disadvantage is that there is limited functionality (other tables cannot be referenced except for foreign KEY constraints), and they need to be redefined again and again for general constraint logic.

SQL Server constraints