Summary of cainiao-Data Integrity

Databases are the warehouses where we store data, and the data we store must be accurate and reliable. When we add, delete, modify, and query the data, we must ensure data integrity and consistency. So let's talk about data integrity.

Let's first look at this figure:

1. Relationship between concepts and functions.

In sqlserver books online, the data integrity is explained as follows: "All data values stored in the database are in the correct state. If an incorrect data value is stored in the database, the database is deemed to have lost data integrity ." That is to say, data integrity is the accuracy and reliability of the index data. The concept and function are integrated. It is a concept proposed to protect data accuracy and reliability.

Ii. Correspondence between categories and methods.

We can see that:

1. Primary Key (primary key constraint) is an embodiment of entity integrity.

2. Domain integrity is required for fields in the table. It corresponds to the data type, not
Null constraint, check bundle, foreign
Key constraint, default constraint, and default value.

3. Integrity of reference: The data of the primary key and the foreign key of the two tables should be consistent.

It has the following functions:

(1) do not insert data rows that contain keywords that do not exist in the primary table.

(2) prohibit the change of foreign key values in the primary table with isolated values from the table.

(3) It is prohibited to delete the records of the master table with corresponding records from the slave table.

Iii. Check constraints and rules.

1. A rule is a backward compatible feature that is used to execute functions that are the same as check constraints.
2. Check constraints are the first standard method to limit column values.
3. Only one rule can be applied to a column, but multiple check constraints can be applied.
4. Rules must be created separately. Check constraints can be created at the same time as tables are created.
5. Rules are more complex than check constraints and more powerful.
6. You can create a rule once and use it multiple times later. It can be applied to tables with multiple columns and user-defined data types.

Iv. Differences between default and default Constraints

The two functions are the same, but the default value is more similar to the rule. It is independent from the table and can be defined once, you can also use user-defined data types.

5. How to Select a method.

Among these methods, triggers have powerful functions, that is, they can maintain basic data integrity logic and complex integrity logic, such as cascade operations for multiple tables, with high overhead; the constraint function is weaker than the trigger, but the overhead is lower. The default and rule functions are weaker, and the overhead is lower. The data type provides the lowest level of data integrity functions, and the overhead is also the lowest.

When selecting an integrity solution, you should follow the solution that selects the overhead when completing the same task. That is to say, triggers are not needed if constraints can be used. Rules are not required if data types can be used to complete the tasks.

The attached figure compares the three types of data integrity implementation methods: