Shulou(Shulou.com)05/31 Report--

This article is about what data integrity means in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Data integrity refers to the accuracy and reliability of data, which means to ensure that information or data is not tampered with without authorization or can be quickly discovered after tampering in the process of transmitting and storing information or data. Data integrity can be divided into four categories: entity integrity, domain integrity, referential integrity, and user-defined integrity.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Data integrity (Data integrity) is one of the three basic points of information security, which means to ensure that information or data is not tampered with without authorization or can be quickly discovered after tampering in the process of transmitting and storing information or data.

Data integrity (Data Integrity) refers to the accuracy (Accuracy) and reliability (Reliability) of data. It is proposed to prevent the existence of data that does not conform to the semantic provisions in the database and to prevent invalid operations or error messages caused by the input and output of error information.

Data integrity can be divided into four categories: entity integrity (Entity Integrity), domain integrity (Domain Integrity), referential integrity (Referential Integrity), and user-defined integrity (User-definedIntegrity).

1. Entity integrity

Entity integrity is one of the three rules of database integrity in relational models. The rule of entity integrity requires that every data table must have a primary key, and all fields as a primary key must have unique and non-null values.

2. Domain integrity

Domain integrity: refers to the input validity of a column and whether null values are allowed. Domain integrity is enforced by restricting the type (by setting the data type of the column), formatting (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions and rules). For example, students' examination scores must be between 0100 and 100, and the gender can only be "male" or "female".

3. Referential integrity

Referential integrity: refers to ensuring the reference relationship between primary keywords (referenced tables) and external keywords (referenced tables). It involves the consistency maintenance of the data of two or more tables.

The foreign key value associates records in the reference table that contain additional keys and records in the referenced table where the primary key matches the foreign key.

When entering, changing, or deleting records, referential integrity maintains defined relationships between tables and ensures that key values are consistent across all tables. Such consistency requirements ensure that non-existent values are not referenced, and if the key value changes, all references to that key value are changed consistently throughout the database.

Referential integrity is based on the relationship between foreign keys and primary keys.

4. User-defined integrity

User-defined integrity is a constraint for a specific relational database, which reflects the semantic requirements that the data involved in a specific application must meet. It mainly includes non-empty constraint, unique constraint, check constraint, primary key constraint and foreign key constraint.

The database uses a variety of methods to ensure data integrity, including foreign keys, constraints, rules and triggers. The system well deals with the relationship between the four, and uses different methods according to different specific conditions, cross-use each other to make up for the shortcomings.

Constraints of integrity

Integrity constraints can be divided into four types: entity integrity constraints, referential integrity constraints, functional dependency constraints and statistical constraints.

1) entity integrity: specifies that each row of the table is a unique entity in the table.

2) Domain integrity: it means that the columns in the table must meet certain data type constraints, which include value range, precision and so on.

3) referential integrity: it means that the data of the primary key and the external key of the two tables should be consistent, which ensures the consistency of the data between the tables and prevents the data loss or meaningless data from spreading in the database.

4) user-defined integrity: different relational database systems often need some special constraints according to their application environments. User-defined integrity is a constraint for a specific relational database, which reflects the semantic requirements that a specific application must meet.

Types of integrity constraints

Can be divided into three types: table-related constraints, Domain constraints, and Assertion

1) Table-related constraints: a constraint defined in a table. You can define this constraint when you define a column, which is called a column constraint, or you can define a constraint when a table is defined, which is called a table constraint.

2) Domain constraint: a constraint defined in a domain definition that is related to any column defined in a particular domain.

3) Assertion: a constraint defined at the time of an assertion that can be associated with one or more tables.

Thank you for reading! This is the end of this article on "what is data integrity in mysql?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.