Shulou(Shulou.com)06/01 Report--

Editor to share with you mysql in the table to set foreign keys to achieve what kind of data integrity, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Setting foreign keys in the table achieves referential integrity.

The integrity of the reference requires that references to entities that do not exist are not allowed in the relationship. And entity integrity is the integrity constraint that the relational model must meet in order to ensure the consistency of the data. Referential integrity is also called referential integrity.

Referential integrity is one of the complete constraints of relational model, which belongs to a kind of data integrity, and the rest are entity integrity and user-defined integrity.

Referential integrity rule: if the attribute or attribute group F is the foreign key of the basic relation R, which corresponds to the primary key Ks of the basic relation S (the basic relation R and S are not necessarily different relationships), the value on F for each tuple in R must be:

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

(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 or null in the referenced relationship, otherwise it does not conform to the semantics of the database. In the actual operation, such as updating, deleting, or inserting data in one table, check whether the data operation on the table is correct by referring to the data in another table that is related to each other. If not, the operation is rejected.

Nature

Referential integrity is an inter-table rule. For tables related to permanent relationships, if you change only one or the other when updating, inserting, or deleting records, the integrity of the data will be affected:

For example, after modifying the key value in the parent table, the key value of the child table does not change accordingly; after deleting a record of the parent table, the corresponding record of the child table is not deleted, so that these records become isolated records; for the records inserted in the child table, there is no record of the corresponding key value in the parent table; and so on. The integrity of data between these design tables is collectively referred to as referential integrity.

Referential integrity is a constraint between two associated tables, specifically, the value of each record foreign key in the slave table must exist in the master table, so if an association is established between the two tables, the operation on one relationship affects the records in the other table.

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

That is, after referential integrity is implemented, the system automatically checks the primary key field to see if it has been added, modified, or deleted when operating on the primary key field in the table. If a change to the primary key violates referential integrity requirements, the system automatically enforces referential integrity.

The above is all the contents of what kind of data integrity is achieved by setting foreign keys in the table by mysql. Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.