MySQL FOREIGN key understanding

A student's personal information table for a class:

What is a foreign keyin 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.

What are foreign keys used for?

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 in the picture you posted, it is too redundant to put the results and student information in a single table.

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 keysthe 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 type as the student table SID, such as the Int (10) type); 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 ; There are related fields (for example, the score table uses the SID of the student table because it is associated, the score table records the student's score, and the student can use the SID to uniquely identify); Avoid using compound keys (that is, a field from a table that can reference multiple primary tables simultaneously as a foreign key, which is generally not recommended).

The foreign key does not need to be the primary key from the table, the foreign key is not necessarily the primary key of the primary table, and the primary table's unique key can be used as a foreign key from the table.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL FOREIGN key understanding