This article uses examples to illustrate the basic functions and usage of MySQL foreign keys. Share with you for your reference, the details are as follows:

In this article:

• What is a foreign key
• Adding foreign keys
• Modification and deletion of foreign keys
• Foreign key constraint mode

Release date: 2018-04-12

What is a foreign key:

• A foreign key is a field in a table that points to the primary key of another table . This field can be called a foreign key.
• A table can have multiple foreign keys.
• Foreign keys are used to constrain the relationship between tables. It can be said that foreign keys are mapping relationships between tables. This relationship can help us deal with the closeness and existence of the relationship between tables ( for example, the cid class number in the student table is associated with the id in the class table. The cid should not be non-existent. If the foreign key cid is not added and the relationship is not established, we will not know that the class number does not exist .).
• In other words, foreign keys tell the database system what we think of as a relationship. The system does not know the actual meaning of pure data, and foreign keys tell the system how to handle their relationship.
• Therefore, the core of the foreign key is a constraint.

Adding foreign keys:

• The prerequisite for creating a foreign key is that the field is first an index. If not, the foreign key will be created as a normal index [so you don't have to worry about it].
• Another prerequisite for creating a foreign key is that the "pointing table" has been created. For a non-existent table, the foreign key cannot be used to correspond to it.
• Ways to increase:
  • 1. Define it when creating the table, and use foreign key references to point to the table (primary key) after all fields are defined, for example
  • 2. You can also modify the fields to add: alter table table name add [constraint foreign key name] foreign key (foreign key field) references parent table (primary key field);
    • constraint foreign key name: can help define the name of the foreign key, but it is not recommended because the foreign key name must be unique, and the system-defined one will never be repeated.

create table student(
id int primary key auto_increment,
name varchar(15) not null,
gender varchar(10) not null,
cid int,
foreign key(cid) references class(id)
);

create table class(
id int primary key auto_increment,
cname varchar(15)
);

Replenish:

• In MySQL, if the storage engine is not innodb, the foreign key constraint cannot take effect, even if the foreign key can be added successfully.
• Foreign key names cannot be repeated, so it is not recommended to use constraint foreign key names.

Modification and deletion of foreign keys:

• Modification: You cannot modify foreign key information, such as what the foreign key points to, you can only delete it first and then add it.
• Deletion syntax: alter table table name drop foreign key foreign key name;
  • The foreign key name here is not the foreign key field, but the foreign key name. If constraint is not used for definition, you can use show create to view the system-defined foreign key name in the table creation statement.
  • 

Replenish:

• When deleting a foreign key, if you use desc, you will see that the table structure also has MUL, which is an index. Because when a foreign key is created, the field will be created as an index. If you don't want to keep it, you can use drop index field name on table name.

Foreign key constraint mode:

• Foreign keys are used to constrain the relationship between tables.
• (The table that creates the foreign key is called the child table, and the table it points to is called the parent table)
  • For child tables: you can constrain the insertion and modification of child tables [this constraint is the constraint of the parent table on the child table]
    • When inserting and modifying foreign keys, if the foreign key field cannot find a corresponding match, the insertion/modification will fail (for example, it is impossible to insert a course that does not exist in the course table when inserting a course selection record).
    • for example:
  • For the parent table: the deletion and update of the parent table can be constrained. Usually there are the following constraint modes. [This constraint is the constraint of the child table on the parent table]
    • model:
      • Strict mode: When it comes to deleting and updating foreign keys, if the primary key data of the corresponding record is already in use by the child table, it cannot be deleted (for example, if someone has already enrolled in a class, the school cannot be stupid enough to delete the class, and can only delete those classes that no one has enrolled in.)
      • Cascade mode: When it comes to foreign key deletion and update, if the field has been used by the child table, the data in the child table will be updated accordingly (for example, if a class number is changed, the class in the student table will be changed accordingly; if a class is deleted, all students in the corresponding class will be deleted)
      • Set null mode: When it comes to foreign key deletion and update, if the field has been used by the child table, the foreign key data in the child table will be set to null (for example, if a class is deleted, all students should not be deleted, but should be set to null first and then reassigned to a class) [The premise for the child table to be set to null is that the field can be allowed to be null]
    • In fact, you can specify different modes for different operations
    • To sum up (based on my examples), in fact, the appropriate measures are to leave it empty when deleting (even if a class is too bad and you want to delete it, you should not drop all students from it, but assign them to other classes), and to cascade when modifying (allowing you to change the class number, and the changes will be updated to the students)
    • How to set different modes for different operations (operating in child tables): foreign key (foreign key field) references parent table (primary key) on operation mode

      foreign key (foreign key field) references parent table (primary key) on delete set null on update cascade; 

-- Experimental table structure create table class(
id int primary key auto_increment,
cname varchar(15)
);
create table student2(
id int primary key auto_increment,
name varchar(15) not null,
gender varchar(10) not null,
cid int,
foreign key(cid) references class(id) on delete set null on update cascade
);
-- Experimental table data:
insert into class(cname) values("python"),("linux"),("java"),("html5");

insert into student2(name,gender,cid) values("Alice","female",1);
insert into student2(name,gender,cid) values("John","female",2);
insert into student2(name,gender,cid) values("Jack","female",3);
insert into student2(name,gender,cid) values("Amy","female",4);

select * from student2;
select * from class;
-- Try to update cascade update class set id = 6 where cname="python";
select * from student2; -- The original python cid=6
-- Try to delete and set to null delete from class where cname="java";
select * from student2; -- The result is that the original Java cid=null

Replenish:

• You need to set the constraint mode properly. Do not use different constraint modes in multiple sub-tables, otherwise there will be conflicts.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.