If a field of one entity points to the primary key of another entity, it is called a foreign key
The entity that is pointed to is called the primary entity (the primary table), also called the parent entity (the parent table).
The entity that is responsible for pointing, called from the entity (from the table), also called the Child Entity (sub-table)
Role:
Used to constrain entities that are within a relationship
When a child table record is added, does it have a parent table record corresponding to it
If the primary table does not have related records, the table cannot be inserted from
Insert the main table data before inserting the data from the table:
How a table should handle related records when deleting or updating a master table record
To set cascading actions:
How the table data associated with the primary table data should be handled when it changes
Use keywords:
On update
On delete
To identify
allowable Cascade actions:
Cascade associated operation, if the primary table is updated or deleted, the corresponding action is also taken from the table
Set NULL, indicating that no records from table data are pointing to the primary table
Restrict: Rejecting the related operation of the primary table
ALTER TABLE T_student add foreign key (class_id) references T_class (class_id)
On delete set null; # When a foreign key is deleted, the foreign key value from the table is set to NULL
To modify a foreign key:
Remove the foreign key from the table before adding
ALTER TABLE Tb_name drop froeign key FOREIGN key name
Foreign key names can be customized when creating foreign keys, and if not customized, a name is automatically generated according to MySQL
Show CREATE TABLE tb_name;
ALTER TABLE t_student drop foreign key t_student_ibfk_1;
Deleting a foreign key does not have any effect on the data in the table, only one constraint on the table is changed
ALTER TABLE T_student add foreign key (class_id) references T_class (class_id)
On delete set null; # When a foreign key is deleted, the foreign key value from the table is set to NULL
Note:on delete and on update can appear at the same time,
However, Cascade, set NULL, restrict cannot occur at the same time after the on delete or on update, there can be only one
FOREIGN key constraints on restrict are not written here, as with the other two
Restrict: Reject the related operation of the primary table, when the primary table updates or deletes data, there is data related to the primary table primary key in the table, then the main table data is not allowed to be updated or deleted
When you do not set any cascading relationship constraints, the primary table defaults to restrict
Under Restrict constraints, if you want to delete the primary table data, in addition to deleting data that is not related to the child table data,
You can modify the foreign key in the child table first (when modified, the foreign key must also be associated to the primary key of the main table, otherwise it cannot be modified successfully)
You can also delete the primary table data that you want to delete before deleting the main table data.