What are the foreign keys in MySQL? And do I need to use a foreign key?

the role of foreign keys

The main role of foreign keys is to maintain data consistency and integrity.

About foreign keys, I have some of my own understanding, but I do not know whether it is correct, for example to express my view: If you need to build a table, what kind of table it? A student's personal information table for a class:

So in the design, you add a foreign key to table 1, the foreign key is the Number field in table 2, then table 1 is the main table, table 2 is the child table. Therefore, the combination of 2 tables will be able to maintain the consistency of the data, integrity (estimated to be restored to the original large table bar).
Take this example to talk about some of the foreign key issues:
1 Table 1 You can have one or more foreign keys, or not. (If you have more than one foreign key in table 1, it is possible to do so, the multiple fields in table 2 are foreign keys to table 1, or the multiple foreign keys in table 1 are in multiple tables)

The above is my personal understanding of the foreign key.

---------------------------------------- ---answer------positive -----------------------------------------

What is a foreign key

+-------+ ref +-------+
| Sub | ------> | Main |
+-------+ +-------+

The value of a column from a table (sub) that references (ref) the primary table (main). For example, the student table has a student number (SID), and the Student column (Stu) in the score table refers to the student number of the student table, at which point the SID of the student table is the foreign key for the Stu of the score table. From the table also called the Foreign key table, the main table is also called the primary key table, the appearance, the column is also called the field.

so in the design, you add a foreign key to table 1, the foreign key is the Number field in table 2, then table 1 is the main table, table 2 is the child table .

Your master-slave relationship is upside down. In your diagram, table 1 is really the main table, table 2 is a child table, but not called to add a foreign key to table 1, but instead of adding a foreign key to table 2, table 2, the Number field is called the foreign key, it is the table 1 Number field of the primary key. You can say this: Table 1 's Study number field is the foreign key of table 2.

What are foreign keys used for?

The picture you posted has been explained. The data recorded for a table should not be too redundant. This is similar to the modular idea of software engineering, except that the table relationship is decoupled in the database, so that the data of the table record is single. As you put in the picture, the results and student information in a table is too redundant, the results can be fully identified with the student ID as a distinguishing mark.

Why foreign keys keep data consistent and complete

You think, the first chapter of your diagram is divided into tables 1 and 2, table 2 of the study number refers to table 1 of the field as a foreign key, if you do not establish a foreign key, just as table 1 simple to set up a number field, then and the establishment of foreign keys what is the difference?

For example, table 1 of the Zhang San number is 20140900001, then I inserted data in table 2 when the study number field 20140900001来 record Zhang San score is not to do the table decoupling it?

The problem here is that, without setting the foreign key, the Number field of table 2 and the Number field in table 1 are not associated. It's just that you think they have a relationship, and the database doesn't think it has anything to do with it. That is, you inserted a value (such as 20140999999) in the Number field in table 2, but this value is not in table 1, and at this point the database is still allowed to be inserted, and it does not check the inserted data. However, in the case of setting the foreign key, you insert the value of the Number field in table 2 must require that the Number field in table 1 be found. Also, if you want to delete a field in table 1, you must ensure that there are no columns in table 2 that reference the value of the field, otherwise you cannot delete it. This is called maintaining the consistency and completeness of the data. You think, if Table 2 also refers to a number of table 1, you have to delete the study number in table 1, table 2 does not know which student is the student who corresponds to the school number. Data consistency also includes data type consistency (see below).

rules for using foreign keys

The field from the table must be the same as the foreign key type (for example, the Stu type of the fractional table must be the same as the student table SID type, such as the Int (10) type) foreign key must be the unique key of the primary table (for example, the student table SID is the primary key, and the primary key is unique, so it can be FOREIGN key) has an associated field (as above, the score table uses the student table's SID because it is associated, the score table records the student's score, and the student can use the SID to uniquely identify) to avoid using the composite key (that is, the table can refer to multiple external fields as a foreign key, which is generally not recommended)

Your question.

If you have more than one foreign key in table 1, it is possible that the multiple fields in table 2 are foreign keys to table 1, or that more than one foreign key in table 1 is in more than one table .

All can. Because the foreign key in table 1 is not necessarily the primary key for table 2, it can also be a unique key (unique). For example, table 2 has a primary key A, a unique key B, table 12 fields a ' and B ' respectively refer to table 2, A and B, which is a many-to-many relationship. or table 2 primary key A, table 3 primary key B, table 1 of two fields a ' and B ' respectively refer to table 2 of a and table 3 B.

foreign keys do not necessarily need to be the primary key from the table, the foreign key is not necessarily the appearance of the primary key, the appearance of the unique key can be used as a foreign key from the table .

Give another picture to help understand

Do you usually need to use a foreign key?

Database design, account number, permissions, constraints, triggers, are designed for the C/s structure, is based on the non-trusted as a premise. The b/S mode security boundary moves forward to the Web service layer, which is trusted between the application and the database, and is more flexible for the application to do by itself. So you don't have to.

1. Internet industry applications do not recommend the use of foreign keys: large user volume, high concurrency, this database server can easily become a performance bottleneck, especially by the IO capability limit, and can not easily expand horizontally; If you put the control of data consistency into a transaction, you let the application server bear this part of the pressure, and the reference server is generally easy to do the horizontal scaling;

2. Database server performance is not a problem, so do not worry about the performance of the problem, in addition, the use of foreign keys can reduce development costs, with the help of the database product itself trigger can achieve the data consistency and update between the table and the associated table, and finally, the use of foreign keys, Can also be a developer and Database Designer Division of labor, can take more work for programmers, why the foreign key has a performance problem:
1. The database needs to maintain the internal management of foreign keys;
2. Foreign key is equal to the data consistency transaction implementation, all to the database server to complete;
3. With the foreign key, as some of the foreign key fields involved in the increase, delete, update operation, the need to trigger the relevant operation to check, and have to consume resources;
4. The foreign key will also be prone to deadlock situations because of the need to request a lock inside other tables;

What are the foreign keys in MySQL? And do I need to use a foreign key?