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

What is the role of MYSQL foreign keys, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

What is the MYSQL foreign key?

Definition: foreign keys are relative to primary keys and are a necessary prerequisite for establishing relationships between tables.

For example:

There are two tables, the user table and the qx table. Gid in user is the id of user rights, while gid depends on id in qx. Then the id in qx is the foreign key of user.

That is, when we create a foreign key for gid, which is the id in qx, gid must be the same as id in qx, and we can associate the two tables with the foreign key.

So what is the purpose of creating a foreign key?

1. It can associate two tables, ensure the consistency of data and realize some cascading operations.

two。 Maintain data consistency and integrity.

The main purpose is to control the data stored in the foreign key table. Associate the two tables, and the foreign key can only refer to the values of the columns in the table! It can associate two tables, ensure the consistency of data and realize some cascading operations.

Does the database design require foreign keys?

Here are two questions:

1. How to ensure the integrity and consistency of database data.

two。 The first article has an impact on performance.

Square point of view

1. The consistency, integrity and reliability of the data are guaranteed by the database itself, because it is difficult for the program to guarantee the integrity of the data 100%.

The use of foreign keys can maximize the consistency and integrity of the data even when the database server crashes or other problems occur.

Note: database and application is an one-to-many relationship, An application will maintain the integrity of his part of the data, when the system becomes larger, add B application, An and B two applications may be done by different development teams. How do they coordinate to ensure the integrity of the data, and what if the C application is added a year later?

two。 Database design with primary and foreign keys can increase the readability of ER diagrams, which is very important in database design.

3. The business logic explained by foreign keys to a certain extent will make the design thoughtful, concrete and comprehensive.

Opposing point of view

1. Data integrity can be guaranteed with triggers or applications

two。 Overemphasizing or using primary / foreign keys will add to the difficulty of development and lead to problems such as too many tables.

3. When not using foreign keys, the data management is simple, the operation is convenient, and the performance is high.

(import and export operations, which are faster when using insert,update and delete data)

Note: in the massive database do not even think about foreign keys, just imagine, a program every day to insert millions of records, when there are foreign key constraints, every time to scan this record is qualified, generally more than one field has foreign keys, so the number of scanning is a series of growth! One of my programs is finished in 3 hours. If you add a foreign key, it takes 28 hours!

Summary

1. In large-scale systems (low performance requirements, high security requirements), use foreign keys; in large-scale systems (high performance requirements, security self-control), do not use foreign keys; small systems are casual, it is best to use foreign keys.

two。 The use of foreign keys should be appropriate, not excessive pursuit.

3. When using programs to control data consistency and integrity, a layer should be written to ensure that each application accesses the database through this layer.

MySQL allows the use of foreign keys, but for integrity checking purposes, this feature is ignored in all table types except the InnoDB table type.

This may be weird, but it's actually quite normal: after each insertion, update, and deletion of all foreign keys in the database, integrity checking is a time-consuming and resource-consuming process that can affect performance. especially when dealing with complex or intertwined connection trees.

Therefore, users can choose the best combination suitable for specific needs on the basis of the table. So, if you need better performance and no integrity check, you can choose to use the MyISAM table type, and if you want to build the table based on referential integrity in MySQL and want to maintain good performance on this basis, it is best to choose the table structure as the innoDB type.

After reading the above, have you mastered the function of MYSQL foreign keys? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.