Foreign key analysis in MySQL (What is foreign key, why use foreign key, add foreign key, primary foreign Key Association Delete)

There's a thing that's been bothering me in my head, but it's not clear that it's going to hinder my progress. Do your own project demo can always use only one sheet ...

So today is still learning the next foreign key hope to understand some ...

Baidu on the role of the search foreign key "to maintain data integrity, consistency ", " referential integrity " ... What , I believe many small partners do not understand what the meaning.

First of all, I need to figure out why to use foreign keys ,

For example, there are two tables (at the bottom I will attach the table to create the SQL statement, as well as the addition of foreign keys and associated DELETE statements)

Table I (STUDENT1) has fields: stu_id (school number), Stu_name (name), Stu_gender (gender), stu_nation (ethnic), Stu_idcard (Social Security Number)

Table II (SCORE1) has fields: stu_id (school number), Stu_chinese (language score), STU_MATH,STU_ENGLISH,STU_PE (Sports results)

By associating stu_id, you can put the table two in the operation of the time can be pieced together into a large table,

Fields:: stu_id (School number), Stu_name (name), Stu_gender (gender), stu_nation (ethnic), Stu_idcard (Social Security Number), Stu_chinese (language score), Stu_math,stu_ English,stu_pe (Athletic results)

Without the foreign key, it seems to be able to operate normally, for example, you can arbitrarily add any information to any table, but if you have a foreign key (Student1 stu_id reference score1 in stu_id)

You won't be able to perform an insert operation on student1 first, because stu_id in Student1 is associated with stu_id in Score1

So you have to first assign the Score1 table to insert into score1 values (' 201890001 ', 95,88,97,95);

It is then possible to insert into Student1 VALUES (' 201890001 ', ' Zhang San ', ' Male ', ' Han ', ' 123468744123597014 '), because when the insert is given to the Student1 table, stu_ The ID must be consistent with the previous assignment to the Score1 table, which means
When assigning values from a table, the associated field values must be found in the main table! Otherwise, add the failure, this is the role of foreign keys!

For example: If I add a student's student score of 1 to Score1, when I add a student information of 2 to the Student1 table, it is added unsuccessfully, because there is no association, unless there is a student score of 2 in Score1. Only in this way can there be referential effect, so the referential integrity is ensured.

Add: What is from the table, what is the main table, STUDENT1 has the statement CONSTRAINT fk_stu_id_score1 FOREIGN KEY (stu_id) REFERENCES score1 (stu_id) means that Score1 is the primary Table, Student1 is from the table (although I am also often dizzy ...). But think, the main table of the associated field must be the primary key, it is larger, it is the main table ... ）
Just mentioned, the associated field of the primary table must be a primary key . This means: Student1 in stu_id and Score1 in the STU_ID Association, score is the primary table, stu_id must be the primary key of the Score1 table, and stu_id in Student1 is not the primary key does not matter.

Next is the deletion of the primary foreign Key association delete:

When the information in the score is deleted, how to solve the related information from the table student1, such as the main Table secondary School Number 1 student performance information is deleted, then from the table student1 the number of students 1 student information, there are three ways to solve:

1, prohibit deletion (do not allow deletion, will error)

2. Empty the data corresponding to the sub-table to delete the data. (The student information for the secondary school number 1 is empty, null) after creating the foreign key, add on DELETE SET null (followed by the Create statement for the practice table)

3. Delete the data corresponding to the sub-table to delete the data. (Deletion of student information from the table School number 1) after creating the foreign key, add the on delete CASCADE

　　　　　　Note If it is empty or deleted, you should be careful to delete or empty, such as you set the field is not NULL, the main table is deleted from the table will also be deleted, but from the table you also defined NOT NULL, so will be an error. This is also the cause of an error found ...

(

In fact, I wrote this only found that I practiced the main foreign key association corresponding table has a logical problem, should be added score1 on the foreign key, let student1 when the main table, score1 when from the table, so when the Student1 secondary school number for 1 of the Student information deleted, score1 from the table secondary number 1 data? Of course it is deleted, you have no students (such as dropping out), but also to save his grade information what to do? Of course, this is a digression, I also do not bother to change from the beginning, I believe we should understand. Logic flip think about it and you'll understand

Or, there's a table for the membership, one for the order table,

Membership table to refer to the information in the order form, they are associated with the Member ID field, when a member of the table of a member information is deleted, according to normal needs, the basic is the order form the corresponding information is also deleted, because you have no member to save the order information what to do? So this is the third solution,

)

Well, back to the previous hypothetical scenario, Student1 is from the table, Score1 is the main table, will wrong down ...

I think I understand the above concept of logic, the other can be a little bit more figured out, such as writing SQL statement error when you can understand why this is the first, to do so ... Next I enclose the SQL statement that I used to practice the table for the experiment.

1  Usetest;2 3 /*To remove the primary table from the table before deleting it (from the table you need to refer to the fields in the primary table, first delete the primary table, then the table is not the same because there is an association with the primary table from the table)*/4 DROP TABLE IF EXISTSStudent1;5 DROP TABLE IF EXISTSScore1;6 7 CREATE TABLEScore1 (8stu_idVARCHAR( -) not NULL,9Stu_chineseDOUBLE,TenStu_mathDOUBLE, OneStu_englishDOUBLE, AStu_peDOUBLE, -     CONSTRAINTPk_stu_id_score1PRIMARY KEY(stu_id) - ); the CREATE TABLEStudent1 ( -stu_idVARCHAR( -), -Stu_nameVARCHAR( -), -Stu_genderVARCHAR(5), +Stu_nationVARCHAR( -), -Stu_idcardVARCHAR( -) not NULL, +     CONSTRAINTPk_stu_idcard_student1PRIMARY KEY(Stu_idcard), A     CONSTRAINTFk_stu_id_score1FOREIGN KEY(stu_id)REFERENCESScore1 (stu_id) on DELETE CASCADE at ); -  - /*The following two INSERT statements in reverse order will cause an error*/ - INSERT  intoScore1VALUES('201890001', the, the, the, the); - INSERT  intoStudent1VALUES('201890001','Zhang San','male','Han','123468744123597014');

This is the two web address that I refer to when I study

1.www.cnblogs.com/bhlsheji/p/5332910.html

2.20898055

Welcome to discuss the next ah, I am also a beginner hope that a lot of advice, what is wrong please point out the discussion ~ Thank you ...