Mysql -- foreign key (froeign key), mysql -- froeign

Mysql -- foreign key (froeign key), mysql -- froeign

If a field of an object points to the primary key of another object, it is called a foreign key.
The object to be pointed to is called a primary object (a primary table) or a parent object (a parent table ).
The entity to which the sub-Table directs is called a sub-table)

 

Purpose:
Used to constrain entities in a link
Whether the corresponding parent table record exists when adding a sub-Table Record

If the master table does not have related records, the slave table cannot be inserted.

 

Insert the data in the master table before inserting the data in the slave table:

 

 

How to process related records from a table when deleting or updating a primary Table Record

Set cascade operations:
How to deal with the associated slave table data when the primary table data changes
Keyword used:
On update
On delete
To identify
Permitted cascade actions:
Cascade Association operation. If the master table is updated or deleted, the slave table also performs the corresponding operation.
Set null indicates that the data in the slave table does not point to any records in the master table.
Restrict: deny operations on the primary table

Alter table t_student add foreign key (class_id) references t_class (class_id)
On delete set null; # When deleting a foreign key, set the foreign key value from the table to null

Modify foreign key:
Delete the foreign key of the table first, and then add
Alter table tb_name drop froeign key foreign key name
The foreign key name can be customized when a foreign key is created. If not, 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 affect the data in the table, but only changes the table's constraints.

Alter table t_student add foreign key (class_id) references t_class (class_id)
On delete set null; # When deleting a foreign key, set the foreign key value from the table to null

 

 

 

Note:On delete and on update can appear at the same time,
However, after on delete or on update, cascade, set null, and restrict cannot appear at the same time. Only one
The foreign key constraint for restrict is not written here, just like the other two.
Restrict: deny operations on the primary table. When updating or deleting data on the primary table, data related to the primary key of the primary table exists in the table, data in the master table cannot be updated or deleted.

When no cascading constraint is set, the primary table will be restrict by default.

 

Under the restrict constraint, if you want to delete data in the primary table, you can delete data that is not related to the child table,
You can modify the foreign key in the sub-table first (the foreign key must also be associated with the primary key of the master table when the modification is made; otherwise, the modification cannot be successful)
 

 

You can also delete the sub-table data of the master table that you want to delete before deleting the data of the master table.

 

 

Add a foreign key to mysql

For tables that contain external keys, you must first create a table that uses external keys as the primary keys before creating your table that contains external keys.
Create table A (aaa int primary key ,...)

Create table B (
Bbb int primary key,
Aaa int,
......
Foreign key (aaa) references A (aaa) on delete... on update...

)
Note that the aaa type and name in B must be exactly the same as those in A. You 'd better have those spaces. Although sometimes it doesn't matter, sometimes it may be wrong.


Mysql primary and foreign key relationships

-- Create a test master table. ID is the primary key.
Create table test_main (
Id INT,
Value VARCHAR (10 ),
Primary key (id)
);

-- Create a test subtable.
Create table test_sub (
Id INT,
Main_id INT,
Value VARCHAR (10 ),
Primary key (id)
);

Default foreign key constraint
Mysql> alter table test_sub
-> Add constraint main_id_cons
-> Foreign key (main_id)
-> REFERENCES test_main (id );
-> //
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0

Mysql> DELETE FROM
-> Test_main
-> WHERE
-> Id = 1;
-> //
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
Nt fails ('test'. 'test _ sub ', CONSTRAINT 'main _ id_cons' foreign key ('main _ id') R
EFERENCES 'test _ main' ('id '))

MySQL uses the following statement to delete a foreign key constraint.
Alter table test_sub drop foreign key main_id_cons;
Reference: hi.baidu.com/...3.html