What is mysql foreign key

mysql foreign key is a very useful data processing function that can quickly update data between tables; simply speaking, it can establish an association between two tables and perform operations When one table is updated, the data in the other table will also change simultaneously.

The operating environment of this tutorial: Windows 10 system, mysql8 version, Dell G3 computer.

What is mysql foreign key?

##Usage of Mysql foreign keys

Foreign key is a very, very easy-to-use thing, and it is also a function that many relational databases have. To put it simply, it can establish an association between two tables, which can be done when operating one table. , the data in another table will also change simultaneously.

Example:

一个学生表里面有学生的所有信息，其中有一个字段是班级id，又有一个班级表，记录着所有的班级信息，按照逻辑来说，如果我们删除了班级
表里面的某个班级，学生表里面是这个班级id的信息就应该修改。

The above example can be completed normally if we use a program. First delete the class, and then add it to the student table. The class id field of the student information of this class is modified and divided into two parts. But if we use foreign keys, it only takes one step. As long as one of the foreign keys is associated, the other one will be automatically updated. Obviously, this is more in line with the program. It is also easier

Details before use

When selecting a storage engine for the database, you must choose a storage engine that adapts to foreign keys, such as the default storage of mysql Engine innodb
1. The field types of related foreign key fields must be consistent. For example, the class_id of the student table is of type int, then the type of class_id of the class table should also be of type int. This is very important.

Create foreign key constraints in the new table

	create table stu_study (
	  sid int primary key auto_increment,-- 定义Sid  
      sname varchar(15) not null,
      course_id int default null,  -- 定义课程id 课程id是外键所以要与关联的主表的字段类型保持一致
      constraint stu_study_class -- 一个表里可能有多个表之间关联，所以外键需要起一个名字
      foreign key (class_id) -- 关联的外键名
      references classes(id)  -- 关联的主表和主表的字段
      on delete cascade  -- 当删除的时候触发
    )engine=InnoDB default charset utf8  -- 默认存储引擎和编码的字符串

In the existing table Add and delete foreign keys in

Delete foreign keys
3. ···
   – Delete foreign keys (foreign key constraints)
   alter table stu_study drop foreign key stu_study_classes;
   ···
   
   Deleting a foreign key is done by deleting it based on the foreign key name in the table. This also indirectly lets us know that the foreign key name cannot be repeated.
   
Add foreign key

4. 	-- 添加外键
     alter table stu_study add
     constraint stu_study_classes  -- 外键名 是一定不能够重复的，通常会用关联的两个表名进行命名
     foreign key(course_id)
     references classes(id)
     on delete cascade;

Adding a foreign key to an existing table is very similar to adding a foreign key when creating a table. There are some details. That is, he needs to use the constraint foreign key name to set the name of the foreign key
We will find that adding and modifying foreign keys is very similar to adding and modifying table fields. The keywords used are add, drop, etc.

Operation of associated actions

Sub table

Main table

We can simply understand the word table and sub table as , the table with the foreign key set is the subtable
There are many kinds of related actions, such as
cascade, set Null, no action, all three It is to set the associated action when setting the foreign key. For example,
represents the action performed when deleting. The main differences are as follows:

1. cascade 删除主表的某个字段的时候，子表含有这个字段的数据会被清空，这个还是属于相对危险的一个操作的
2. +set null 删除主表的某个字段的时候，子表含有这个字段的这个哪一行的这个字段会用null来显示，但是有个细节就是设置外键的这个字段字段类型就不能设置为not null类型的，否则会报错
3. no action 这个字段会比较有意思，也就是说当主表要删除某个行的时候，如果外键关联有含有这个主表的外键的字段数据的话，就不会删除成功，系统会直接报错

关联更新操作

之前是主要讲了关联删除，是因为外键在使用的时候关联删除操作是使用的比较频繁的，关联更新的频率是相对来说低一点儿的
关联更新的三个关联操作和删除时一样的，分别是cascade,set null,no action意思是也是一样的，主表某个字段更新了，子表也会更新那个字段_！！！，主表更新的某条数据，子表的使用的那个数据会变成空，和子表在使用的情况下，主表就不能够更新数据

	alter table stu_study drop foreign key stu_study_classes;  -- 删除外键
	-- 添加外键
	alter table stu_study add 
    constraint stu_study_classes 
    foreign key (course_id) 
    references classes(id) 
    on delete set null -- 一次性设置外键的两种动作
    on update cascade

总结

• 数据库的外键是非常非常好用的一个技术，可以让我们快速的进行表之间的数据的更新
• 外键可以简单的理解成会自动的替我们做一个数据变动的处理

推荐学习：《MySQL视频教程》

The above is the detailed content of What is mysql foreign key. For more information, please follow other related articles on the PHP Chinese website!