What are the foreign keys in MySQL and what are the effects?

recently self-study database MySQL, and then have a question, has not been its solution, query the relevant information, and finally there is no solution.
my question is " use FOREIGN KEY constraint ", then I do not understand the word " foreign key ", query related information is to say some terminology, said the main role of foreign key is: to maintain data consistency, integrity . Listen to me as a confused.
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 can have one or more foreign keys, also can 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)
2. this foreign key may not be the primary key of table 1, but it must be the primary key of the child table . (Simply put, if a field is a foreign key to a table, the field must be a primary key)

The above is my personal understanding of the foreign key.

---------------------------------------------Answer-------------------------------------

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).

usage Specification for foreign keys

1. The field from the table must be the same as the foreign key type (as above, the type of the fractional table Stu must be the same type as the student table SID, for example, the Int (10) type)
2. The foreign key must be a unique key for the primary table (for example, the student table SID is the primary key, and the primary key is unique, so it can be used as a foreign key for the Stu of the score table)
3. There are associated fields (such as above, where the score table uses the SID of the student table because it is related, the score table records the student's score, and the student can use the SID to uniquely identify )
4. Avoid using composite keys (that is, you can refer to multiple external fields as a foreign key from a table, which is generally not recommended)

your question.

1. 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.
   

2. This foreign key may not be the primary key of table 1, but must be the primary key of the child table. (Simply put, if a field is a foreign key to a table, the field must be a primary key)

Because you understand the mistake in front of you, so this sentence itself is wrong. For the table, the foreign key does not 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