What is the foreign key in MySQL? _ MySQL

What is the foreign key in MySQL? what is the role of the foreign key in Mysql?

This article participates in the blog contest. if you are satisfied, click here to vote for me. Click here to view the original article.I recently learned the MySQL database by myself. I had a question, but I had never been able to solve it. after querying the relevant information, I still did not solve the problem.
My question is "using foreign key constraints", and I am not very familiar with the word "foreign key". The query related materials are all about some terms. The main role of foreign keys is: maintain data consistency and integrity. I was confused.

I have some understanding about foreign keys, but I don't know if they are correct. for example, I would like to express my opinion: What kind of table should I create now? The student personal information table of a class:

Therefore, at the design stage, add a foreign key to Table 1, which is the student ID field in table 2. in this way, Table 1 is the master table and table 2 is the sub table. Therefore, we can combine two tables to maintain data consistency and integrity (it is estimated that the original large table will be restored ).
In this example, let's talk about some things about foreign keys:
1. Table 1 can have one or more foreign keys. (Can this be the case if Table 1 has multiple foreign keys? multiple fields in table 2 are foreign keys in Table 1; or multiple foreign keys in Table 1 are in multiple tables)
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 of a table, the field must be a primary key)

The above is my personal understanding of foreign keys.

----------------------------------------Solution --- answer --- correction --- positive-----------------------------------------

What is a foreign key?

+ ------- + Ref + ------- +
| Sub | ------> | main |
+ ------- ++ ------- +

Reference the value of a column in the main table (ref) from a column in the table (sub. For example, the student table has a student ID (sid). The Student column (stu) in the score table references the student ID in the student table. in this case, for the stu in the score table, the student table sid is a foreign key. A table is also called a foreign key table. a primary table is also called a primary key table, an external table, and a column is also called a field.

Therefore, at the design stage, add a foreign key to Table 1, which is the student ID field in table 2. in this way, Table 1 is the master table and table 2 is the sub table..

Your understanding of the master-slave relationship is reversed. In your figure, Table 1 is indeed a master table, and Table 2 is a sub table, but it is not called adding a foreign key to Table 1, but adding a foreign key to Table 2, the student ID field in table 2 is called a foreign key, which is the primary key of the student ID field in Table 1. You can say that the student ID field of Table 1 is the foreign key of table 2.

What are foreign keys used?

The picture you posted has already 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, and the data recorded in the table is minimized to be single. As shown in the picture you posted, placing the score and student information in a table is too redundant. the score can be distinguished by the student id.

Why does the foreign key maintain data consistency and integrity?

Think about it. in your figure, the first chapter of the table is divided into tables 1 and 2. the student ID of table 2 references the student ID field of Table 1 as the foreign key. If no foreign key is created, just as simple as setting up a student ID field in Table 1, what is the difference with setting up a foreign key?

For example, if the student ID of Michael in Table 1 is 20140900001, when I inserted data in table 2, I inserted 20140900001 in the student ID field to record Michael's score. isn't table decoupling achieved?

The problem is that the student ID field in table 2 is not associated with the student ID field in Table 1 without setting a foreign key. You only think they have a relationship, and the database does not think they have a relationship. That is to say, you have inserted a value (such as 20140999999) in the student ID field of table 2, but this value does not exist in Table 1. at this time, the database still allows you to insert, it does not perform a link check on the inserted data. However, when you set a foreign key, the value of the student ID field in table 2 must be found in the student ID field in Table 1. At the same time, if you want to delete a student ID field in Table 1, you must ensure that the column in Table 2 does not reference the value of this field. Otherwise, you cannot delete it. This is called maintaining data consistency and integrity. If Table 2 still references a student ID in Table 1, but you delete the student ID in Table 1, table 2 does not know which student the student belongs. Data consistency also includes data type consistency (as shown below ).

The foreign key usage rules must be of the same type as the foreign key fields in the table. (for example, the stu type in the score table must be the same as that in the student table sid. for example, all fields are of the int (10) type) the foreign key must be the unique key of the primary table (as shown above, the student table sid is the primary key and the primary key is unique, so it can be used as the foreign key of the score table stu, the student table sid is used in the score table because the two are associated. the score table records the student's score, and the student can use the sid to uniquely identify the score) avoid using a composite key (that is, a table can reference multiple external fields as a foreign key at the same time. This is generally not recommended)

Your problem

If Table 1 has multiple foreign keys, can this be the case? multiple fields in table 2 are foreign keys in Table 1; or multiple foreign keys in Table 1 are in multiple tables..

Yes. 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 and A unique key B. The two fields A' and B 'in Table 1 reference A and B in table 2 respectively, this is the relationship between many and many. Alternatively, the two fields A' and B 'in table 2, primary key A in Table 3, primary key B in Table 1 reference A in table 2 and B in Table 3 respectively.

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 of a table, the field must be a primary key)

This sentence is wrong because you have understood it before. For a table, the foreign key does not need to be used as the primary key of the slave table. the foreign key is not necessarily the primary key of the external table. the foreign key of the external table can be used as the foreign key of the slave table.

Give a picture to help you understand