Creation, constraints and deletion of foreign keys in MySQL

Preface

After MySQL version 3.23.44, InnoDB engine type tables support foreign key constraints.

Conditions for using foreign keys:

1. Both tables must be InnoDB tables. MyISAM tables do not support foreign keys for the time being (it is said that future versions may support it, but at least not currently);

2. The foreign key column must have an index. MySQL 4.1.2 and later versions will automatically create an index when creating a foreign key, but if you use an earlier version, you need to explicitly create an index.

3. The columns of the two tables in the foreign key relationship must have similar data types, that is, columns that can be converted to each other, such as int and tinyint, but not int and char;

Benefits of foreign keys: they can associate two tables, ensure data consistency, and implement some cascading operations;

1. Creation of foreign keys

Syntax 1: Subsequent addition method

alter table table name add constraint constraint name foreign key (the field constrained in the current table) references main table name (the field name to be constrained);
alter table student add constraint fk_class_student foreign key(cls_id) class(cls_id) on update cascade on delete no action;

Syntax 2: How to create a table

CREATE TABLE student(
sid int PRIMARY KEY,
cls_id int not null,
sname varchar(10) not null,
constraint fk_class_student foreign key(cls_id) references class(cls_id) on update cascade on delete no action
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

An additional point:

SHOW CREATE TABLE class

You can query the table creation information

CREATE TABLE `class` (
 `cls_id` int NOT NULL,
 `cls_name` varchar(15) NOT NULL,
 PRIMARY KEY (`cls_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

In the following demonstration, we will use the student table and class table we just created.

The content is as follows

2. About the Four Constraint Methods

When performing update/delete operations on the parent table, the operation type of the child table

1. CASCADE The subtable will delete all data that contains references to the deleted key value.
2. SET NULL When the parent table is deleted or updated, the child table will set the column where the foreign key field of the associated record is located to null
3. RESTRICT Rejects deletion requests for fields with relationships (this is the default and safest setting)
4. NO ACTION is similar to RESTRICT
   

Take the foreign key we just set as an example (on update cascade on delete no action)

When we try to delete

delete from class WHERE cls_id=1

The results are as follows

It can be seen that due to the existence of on delete no action in the foreign key constraint, deletion operations on the main table are not allowed. But the subtable can

delete from student WHERE cls_id=1

When we update the associated key of the parent table, it can be updated normally due to the existence of on update cascade

UPDATE class set cls_id=4 where cls_id=1

And as the main table is updated, the foreign key fields in the child table are also updated

3. How to delete foreign keys

alter table subtable name drop foreign key foreign key constraint name alter table student drop foreign key fk_class_student

After we delete the foreign key, the operation of the parent table becomes normal.

Summarize

This is the end of this article about foreign key creation, constraints, and deletion in MySQL. For more information about MySQL foreign key creation, constraints, and deletion, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!