Shulou(Shulou.com)06/01 Report--

Preface

To establish a primary foreign key for an mysql data table, you need to pay attention to the following points:

The storage engine for the two tables that need to establish a primary foreign key relationship must be InnoDB. Foreign key 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 create an index, and if the foreign key column does not have an index, mysql will automatically create the index.

1. 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), method 1: without specifying the foreign key name, the database automatically generates foreign key (CharID) references ChineseCharInfo (ID) on delete cascade on update cascade-method 2: specify the foreign key name as (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

Second, when the datasheet 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 the foreign key to the field (CharID) in the table (demo.ChinesePinyinInfo) without specifying the foreign key name, and the database automatically generates the foreign key name alter table demo.ChinesePinyinInfo add foreign key (CharID) references ChineseCharInfo (ID)

Delete the primary foreign key constraint

-- delete the self-growth by modifying the properties of the column. 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;-- to delete the primary key constraint in the table (demo.ChinesePinyinInfo). If the primary key is listed as a self-incrementing column, you need to delete the self-growing alter table demo.ChinesePinyinInfo drop primary key of the column first. Delete the foreign key alter table demo.ChinesePinyinInfo drop foreign key FK_Name with the name (FK_Name) from the table (demo.ChinesePinyinInfo)

IV. Constraints of primary and foreign key relations

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 to update or delete any foreign key values that exist or match in any 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 rows, the child table deletes or updates the rows that match the main table at the same time, that is, cascading delete and update. Set null: if the main table deletes or updates the corresponding data and, the child table also sets the foreign key column of the row that matches the main table to null. Invalid 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 primary table. If no action for on delete or on update is specified, the default action for on delete or on update is restrict.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.