What type of data integrity is achieved by setting foreign keys in the table?

Setting foreign keys in the table achieves referential integrity.

Referential integrity requires that references to non-existing entities are not allowed in the relationship. Entity integrity is an integrity constraint that the relational model must meet to ensure data consistency. Referential integrity is also called referential integrity. (Recommended learning: MySQL video tutorial)

Referential integrity is one of the complete constraints of the relational model and is a type of data integrity. The others include: Entity integrity, user-defined integrity.

Referential integrity rules: If an attribute or attribute group F is a foreign key of the basic relationship R, it corresponds to the primary key Ks of the basic relationship S (the basic relationships R and S are not necessarily Different relationships), then the value of each tuple in R on F must be:

(1) Null value, each attribute value of F is a null value.

(2) The primary key value (primary code value) in a tuple in S.

That is, the attribute value in the referenced relationship must be found in the referenced relationship or take a null value, otherwise it does not comply with the semantics of the database. In actual operations, such as updating, deleting, and inserting data in a table, check whether the data operation on the table is correct by referencing the data in another table that is related to each other. If it is incorrect, the operation will be rejected

Properties

Referential integrity belongs to inter-table rules. For related tables with permanent relationships, when updating, inserting or deleting records, if only one is changed but not the other, the integrity of the data will be affected:

For example, modifying the parent table After the key value is entered, the key value of the child table does not change accordingly; after deleting a record in the parent table, the corresponding record in the child table is not deleted, causing these records to become orphan records; for records inserted into the child table, there are no records in the parent table Records of corresponding keyword values; and so on. The integrity of data between these design tables is collectively called referential integrity.

Referential integrity is a constraint between two related tables. Specifically, the value of the foreign key of each record in the slave table must exist in the master table. Therefore, If a relationship is established between two tables, operations on one relationship will affect records in the other table.

If referential integrity is implemented, records cannot be added to related tables when there are no related records in the main table. You cannot delete records in the main table when there are matching records in the related table, nor can you change the primary key value in the main table when there are related records in the related table.

In other words, after referential integrity is implemented, the system will automatically check the primary key field when operating on the primary key field in the table to see whether the field has been added, modified, or deleted. If a modification to the primary key violates referential integrity requirements, the system automatically enforces referential integrity.

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of What type of data integrity is achieved by setting foreign keys in the table?. For more information, please follow other related articles on the PHP Chinese website!