Detailed explanation of creating a data table in MySQL and establishing primary and foreign key relationships

Preface

When creating a primary and foreign key for a MySQL table, you need to pay attention to the following points:

• The storage engine of the two tables that need to establish a primary and foreign key relationship must be InnoDB.
• The foreign key column and the referencing column must have similar data types, that is, data types that can be implicitly converted.
• Foreign key columns and reference columns must have indexes created. If an index does not exist for a foreign key column, MySQL will automatically create an index.

1. SQL statement to create a data table and set the primary and 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),
-- Method 1: Do not specify a foreign key name, the database automatically generates a foreign key (CharID) references ChineseCharInfo(ID) on delete cascade on update cascade 
-- Method 2: Specify the foreign key name (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 and foreign key relationship

-- Add a foreign key to the field (CharID) in the table (demo.ChinesePinyinInfo) and specify the foreign key name as (FK_Name)
alter table demo.ChinesePinyinInfo add constraint FK_Name foreign key (CharID) references ChineseCharInfo(ID);
-- Add a foreign key to the field (CharID) in the table (demo.ChinesePinyinInfo). Do not specify the foreign key name. The database will automatically generate the foreign key name. alter table demo.ChinesePinyinInfo add foreign key (CharID) references ChineseCharInfo(ID);

3. Delete primary and foreign key constraints

-- Delete the auto-increment by modifying the column properties. The first (ID) is the original column name, and the second (ID) is the new column name. alter table demo.ChinesePinyinInfo change ID ID int not null;
-- Delete the primary key constraint in the table (demo.ChinesePinyinInfo). If the primary key column is an auto-increment column, you need to delete the auto-increment of the column first. alter table demo.ChinesePinyinInfo drop primary key;
-- Delete the foreign key named (FK_Name) in the table (demo.ChinesePinyinInfo) alter table demo.ChinesePinyinInfo drop foreign key FK_Name;

  4. Constraints of primary and foreign key relationships

If the child table attempts to create a foreign key value that does not exist in the primary table, the database will reject any insert or update operation.

If the primary table attempts to update or delete any existing or matching foreign key values ​​in the child table, the final action depends on the on delete and on update options in the foreign key constraint definition.

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

• cascade: If the main table deletes or updates the corresponding data row, the sub-table will also delete or update the rows that match the main table, i.e. cascade deletion and update.
• set null: If the main table deletes or updates the corresponding data, the sub-table will also set the foreign key columns of the rows matching the main table to null. Has no effect when the foreign key column is set to not null.
• no action: The database refuses to delete or update the primary table.
• restrict: The database refuses to delete or update the main table. If the on delete or on update action is not specified, the default action of on delete or on update is restrict.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.