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

Recent self-learning database MySQL. Then there is a question. has not been the solution. The relevant information was queried. Finally, there is no solution.
My question is "use foreign key constraints", and then I don't understand the word "foreign key" very well. Query the relevant information is to speak some terminology. The main function of the foreign key is to maintain the consistency and completeness of the data. 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, give a sample to express my view: If you need to build a table now, what kind of table? A student's personal information table for a class:

So at the time of design. Add a foreign key to table 1, which is the Number field in table 2. So table 1 is the main table, and table 2 is the child table. So the combination of 2 tables will be able to maintain the consistency of the data, integrity (is expected to revert to the original large table bar).
Take this example and talk about some of the foreign key issues:
1, table 1 can have one or more foreign keys, also can not. (Assuming that table 1 has multiple foreign keys can not be the case.) The multiple fields in table 2 are foreign keys to table 1, or multiple foreign keys in table 1 are in more than one table
2, this foreign key can not be the primary key of table 1, but must be the primary key of the child table. (in short, that is.) Suppose a field is a foreign key to a table. Then 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 Stu for the score table. The SID of the student table is the foreign key.

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 at the time of design. Add a foreign key to table 1, which is the Number field in table 2. So table 1 is the main table, and 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 is not called to table 1 to add a foreign key, but to table 2 to add a foreign key, 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 almost identical to the modular idea of software project, which simply decouples the table relationships in the database and single the data that the table records as much as possible. As you put in the picture, the results and student information in a table is too redundant, the results can be completely in the student's ID as a distinguishing mark.

Why foreign keys keep data consistent and complete

You think, the first chapter of your chart is cut into table 1 and table 2, table 2 of the study number refers to table 1 as the foreign key, assuming that the foreign key is not established. Just as in table 1, set up a field of study number simply. So what's the difference between creating a foreign key?

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

The problem here is. Without setting the foreign key. There is no association between the number field in table 2 and the Study Number field in table 1. It's just that you think they have a relationship. The database doesn't seem to have anything to do with it. That is, you inserted a value (for example, 20140999999) in the Number field in table 2, but this value is not in table 1, and this time, 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. The values you insert in the Table 2 field are required to be found in the Number field in table 1. At the same time. Suppose you want to delete a number field in table 1. You must ensure that there are no columns in table 2 that reference the value of the field, or you cannot delete it.

This is called maintaining the consistency and completeness of the data. You want to. If Table 2 also refers to a study number in table 1, you have to delete the study number in table 1, table 2 does not know the student number corresponding to which student.

The consistency of the data also includes the consistency of the data type (see below).

Usage specification for foreign keys

1. The fields from the table must be the same as the foreign key type (as above.) The type of the score table Stu must be the same as the type of the student table SID, for example, the Int (10) type)
2. The foreign key must be a unique key for the primary table, as above. The Student table SID is the primary key, and the primary key is unique. So it can be used as a foreign key to stu 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 uniquely identify with the SID)
4. Avoid using composite keys (that is, it is generally not recommended to refer to multiple external fields as a foreign key from the table at the same time)

Your question.

1. Assuming that there are multiple foreign keys in table 1, it is not possible to do this, and the multiple fields in table 2 are foreign keys to table 1, or the multiple foreign keys for table 1 are in more than one table .

   All can. Because the foreign key of table 1 is not necessarily the primary key of 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 can not be the primary key of table 1, but must be the primary key of the child table. (in short, that is.) If a field is a foreign key to a table, the field must be a primary key)

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

http://www.cnblogs.com/bhlsheji/p/5332910.html#3827915

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