Database basics-Data Integrity completion-constraints and rules

The data in the database is input from outside, and the input is invalid or incorrect for various reasons. Ensuring that the input data meets the requirements has become a top concern for database systems, especially multi-user relational database systems. Therefore, data integrity is proposed. This chapter describes the concept of data integrity and its implementation methods in SQL Server.
Data integrity is the accuracy and reliability of data ). It should prevent data that does not comply with the semantics of the database and prevent invalid operations or error messages caused by incorrect information input and output. There are four types of data integrity: entity integrity and domain integrity.

Domain integrity, referential integrity, and user-defined integrity ).

9.1.1 entity integrity (entity integrity)

Entity integrity specifies that each row of a table is a unique entity in the table. The unique primarykey and identity constraints defined in the table are the embodiment of entity integrity.

9.1.2 domain integrity)

Domain integrity means that columns in a database table must meet certain data types or constraints. The constraints include the value range and accuracy. The check, foreign key constraints, and default and not null definitions in the table all fall into the category of domain integrity.

9.1.3 referential integrity)

Integrity of reference means that the primary Keywords of the two tables are consistent with those of the foreign keywords. It ensures that the tables with primary keywords correspond to rows with external keywords from other tables, that is, it ensures data consistency between tables, prevents data loss or meaningless data from spreading in the database. The integrity of the parameter is based on the relationship between the external keyword and the primary keyword or between the external keyword and the uniqueness keyword. In SQL Server, the integrity of the reference is manifested in the following aspects:

You are prohibited from inserting data rows from the table that contain keywords that do not exist in the master table;

The prohibition will change the value of the foreign keywords in the master table that are isolated from the corresponding values in the table;

It is prohibited to delete the primary table records with corresponding records in the slave table.

9.1.4 User-Defined integrity)

Different relational database systems require special constraints depending on their application environments. User-Defined integrity is the constraint for a specific relational database. It reflects the semantic requirements that must be met by the data involved in a specific application. SQL Server provides a mechanism to define and verify such integrity so that they can be processed in a unified system approach, rather than using applications to undertake this function. Other integrity types support user-defined integrity.

SQL Server provides some tools to help you implement data integrity. The most important tools are rules, default, constraint, and trigger ). Constraints are described in the previous section, and triggers are described in later sections. This chapter describes rules and default values.

(Rule) Rules are the database's rules and restrictions on the values of columns stored in tables or user-defined data types. Rules are independent database objects stored separately. Rules are independent from the tables or user-defined data types they are applied to. deletion or modification of tables or user-defined objects does not affect the rules associated with them. Rules and constraints can be used at the same time. A table column can have one rule and multiple check constraints. The rules are similar to the check constraints. In contrast, using the check constraints in the altertable or create table command is a more standard method to limit column values, however, the check constraint cannot directly apply to user-defined data types.
9.2.1 create a rule

(1) Use the create rule command to create a rule

The create rule command is used to create rules in the current database. Its syntax is as follows:

Create rule rule_name as condition_expression

The condition_expression clause is the rule definition. A condition_expression clause can be any expression used in a where Condition Clause. It can contain Arithmetic Operators, Relational operators, and predicates (such as in, like, and ).

Note: The expression in the condition_expression clause must start with the character.

　　

(2) create rules with Enterprise Manager

In Enterprise Manager, right-click the database object "rules" and choose "newrule" from the shortcut menu. The create rule attribute dialog box shown in 9-1 is displayed. After entering the rule name and expression, click OK to create the rule.

　　

9.2.2 view rules

(1) view rules with Enterprise Manager

Select the "Rules" Object in Enterprise Manager to view most of the Rule Information from the task board on the right, including the rule name, owner, creation time, and so on. In SQL Server 2000, unlike version 7.0, you can view the rule expression in the task board directly, which requires you to view the rule attributes. You can select the rule you want to view, right-click the rule, and select the "properties" option from the shortcut menu. the dialog box shown in 9-3 is displayed, from which you can edit the rule expression. You can use the sp_rename system stored procedure to modify the rule name. Alternatively, you can right-click the rule you want to modify in Figure 9-2 and choose Rename from the shortcut menu) to modify the name.

(2) Use the Stored Procedure sp_helptext to view rules

You can use the sp_helptext stored procedure to view the details of the rule. The syntax is as follows:

Sp_helptext [@ objname =] 'name'

The [@ objname =] 'name' clause specifies the object name.

It is the rules, default values, triggers, views, or unencrypted stored procedures in the current database.

　　

Example 9-5: view the hire_date_rule rule.

Exec sp_helptext hire_date_rule

The running result is as follows:

Text

Bytes --------------------------------------------------------------------------------------

Create rule hire_date_rule

As @ hire_date> = '2017-01-01 'and @ hire_date <= getdate ()

9.2.3 binding and unbinding rules

After a rule is created, the rule is only an object in the database and does not take effect. You need to associate rules with database tables or user-defined objects to create rules. The contact method is called "binding ". The so-called binding is to specify which column or user-defined data type the rule applies. One column or user-defined data type in a table can only be bound to one rule, and one rule can be bound to multiple objects, which is the charm of the rule. The unbinding of a rule and an object is called "unbinding ".

(1) Use the Stored Procedure sp_bindrule to bind rules

The storage procedure sp_bindrule can bind a rule to a column or a user-defined data type in the table. The syntax is as follows:

Sp_bindrule [@ rulename =] 'rule ',

[@ Objname =] 'object _ name'

[, 'Ureureonly']

Parameters are described as follows:

[@ Rulename =] 'rule'

Specify the rule name.

[@ Objname =] 'object _ name'

Specify the objects bound to the rule.

'Ureureonly'

This option can only be used when binding rules to user-defined data types. When this option is specified, new rules will be applied to columns that use this custom data type only later. columns that currently use this data type are not affected.

Example 9-6: bind the hire_date_rule rule to the custom data type hire_date.

Exec sp_bindrule hire_date_rule, hire_date

The running result is as follows:

Rule bound to data type.

The new rule has been bound to column (s) of the specified user data type.

Example 9-7: bind the hire_date_rule rule to hire_date of the User-Defined data type, with the 'ureureonly' option.

Exec sp_bindrule hire_date_rule, hire_date, 'ureureonly'

The running result is as follows:

Rule bound to data type.

Example 9-8: bind the rule my_rule to the order_id field of the orders table.

Exec sp_bindrule id_rule, 'Orders. [order_id]'

The running result is as follows:

Rule bound to table column.

Note: The rule does not work for data in the input table.

The data type specified by the rule must be the same as the data type of the bound object, and the rule cannot be bound to a column whose data type is text, Mage, or timestamp.

Rules bound to columns in a table take precedence over those bound to custom data types. Therefore, if the data type of a column in a table is bound to rule, when the column is bound to rule B, rule B is used as the column.

You can use a new rule to bind a column or user-defined data type. Instead of removing the previously bound rule, the system will overwrite the morning shift rule.

(2) Use the Stored Procedure sp_unbindrule to unbind the rule.

Sp_unbindrule can unbind rules from columns or custom data types. Its syntax is as follows:

Sp_unbindrule [@ objname =] 'object _ name'

[, 'Ureureonly']

The 'ureureonly' option is used only for user-defined data types, the same as when it is bound. It specifies that existing columns defined using this user-defined data type are still bound to this rule. If this option is not specified, all columns defined by the user-defined data type will be unbound from this rule.

Example 9-9: unbind hire_date_rule from the custom data type birthday with 'ureureonly'

.

Exec sp_unbindrule birthday, 'ureureonly'

The running result is as follows:

(1 row (s) affected)

Rule unbound from data type.

(3) bind management rules with Enterprise Manager

In Enterprise Manager, select the rule to bind, right-click, and select "properties" from the shortcut menu. The rule Properties dialog box shown in 9-3 is displayed. "Bind udts…" in the figure ..." The button is used to bind the user-defined data type, "bind columns ..." Button is used to bind a table column.

In Figure 9-3, click "bind udts ..." The binding rules shown in 9-4 to the Custom Data Type dialog box appears. Click "bind columns ..." Button to display the dialog box for binding rules to columns in the table shown in 9-5.

　　

9.2.4 delete a rule

You can select a rule in Enterprise Manager, right-click the rule, select the delete option from the shortcut menu to delete the rule, or use the drop rule command to delete one or more rules in the current database. The syntax is as follows:

Drop rule {rule_name} [,... n]

Note: before deleting a rule, you must unbind the bound object.

Example 9-10: delete multiple rules

Drop rule mytestrule rule, mytest2_rule

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/10/11/1820052.aspx