MySQL Create Data Table and Establish Primary Foreign Key Relationship Detailed Explanation

Preface

The following points need to be noted when establishing primary and foreign keys for mysql data tables:

The storage engine of the two tables that need to establish the primary foreign key relationship must be InnoDB. Foreign key columns and reference columns must have similar data types, that is, data types that can be implicitly converted. The foreign key column and reference column must be indexed, and mysql automatically creates an index if no index exists for the foreign key column.

1. The SQL statement creates a data table and sets the primary-foreign key relationship

create table demo.ChineseCharInfo
(
ID int not null auto_increment,
Hanzi varchar(10) not null,
primary key (ID)
)
engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;
create table demo.ChinesePinyinInfo
(
ID int not null auto_increment,
CharID int null,
Pinyin varchar(10) null,
Tone tinyint unsigned null,
primary key (ID),
--  Mode 1 Do not specify a foreign key name, automatically generated by the database 
foreign key (CharID) references ChineseCharInfo(ID) on delete cascade on update cascade 
--  Mode 2 Specifies that the foreign key name is (FK_Name)
-- constraint FK_Name foreign key (CharID) references ChineseCharInfo(ID) on delete cascade on update cascade 
)
engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;

2. When the data table already exists, use the following method to establish the primary foreign key relationship

--  Is a table (demo.ChinesePinyinInfo) Middle field (CharID) Add a foreign key and specify that the foreign key name is (FK_Name)
alter table demo.ChinesePinyinInfo add constraint FK_Name foreign key (CharID) references ChineseCharInfo(ID);
--  Is a table (demo.ChinesePinyinInfo) Middle field (CharID) Add foreign key, do not specify foreign key name, and automatically generate foreign key name by database 
alter table demo.ChinesePinyinInfo add foreign key (CharID) references ChineseCharInfo(ID);

3. Delete the primary foreign key constraint

--  Delete self-growing by modifying the attributes of columns, the 1 A (ID) For the original column name, article 2 A (ID) For the new column name 
alter table demo.ChinesePinyinInfo change ID ID int not null;
--  Delete table (demo.ChinesePinyinInfo) If the primary key column is a self-growing column, you need to delete the self-growing column first 
alter table demo.ChinesePinyinInfo drop primary key;
--  Delete table (demo.ChinesePinyinInfo) The name in is (FK_Name) Foreign key of 
alter table demo.ChinesePinyinInfo drop foreign key FK_Name;

4. Constraints on primary-foreign key relationships

If the child table attempts to create a foreign key value that does not exist in the main table, the database rejects any insert or update operations.

If the primary table attempts a foreign key value that exists or matches in any of the child tables of update or delete, the final action depends on the on delete and on update options in the foreign key constraint definition.

on delete and on update both have the following four actions.

cascade: If the primary table deletes or updates the corresponding data rows, the child table deletes or updates the rows matching the primary table at the same time, that is, cascading deletion and update. set null: If the primary table deletes or updates the corresponding data sum, the child table also sets the foreign key column of the row that matches the primary table to null. Invalid when the foreign key column is set to not null. no action: The database refused to delete or update the main table. restrict: The database refuses to delete or update the main table. If the action of on delete or on update is not specified, the default action of on delete or on update is restrict.