MySQL foreign key usage and explanation

First, FOREIGN KEY constraints

MySQL uses foreign key constraints to ensure the integrity and accuracy of data between tables.

Use conditions for foreign keys:

1. Two tables must be a InnoDB table, the MyISAM table temporarily does not support foreign keys (it is said that later versions may be supported, but at least not currently supported);

2. The foreign key column must be indexed, MySQL 4.1.2 later version will automatically create the index when the foreign key is established, but if the earlier version needs to display the establishment;

3. The columns of the two tables of the foreign-key relationship must be of similar data types, i.e., columns that can be converted to each other, such as int and tinyint, and int and char are not allowed;

Benefits of foreign keys:

Can make two tables association, ensure the consistency of data and realize some cascade operation;

Definition syntax for foreign keys:

?

1234	[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)REFERENCES tbl_name (index_col_name, ...)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

This syntax can be used when creating table and ALTER table, and if you do not specify constraint Symbol,mysql automatically generates a name.

On DELETE, the on update indicates the event-triggering limit, and can be set with parameters:

RESTRICT (Restrict foreign key changes in appearance)
CASCADE (following foreign key changes)
Set NULL (null value set)
Set default (Set defaults)
No action (no action, default)

A simple demo to use, do dage and xiaodi two tables, Big Brother table is the primary key, the younger brother table is a foreign key

Build table:

?

12345678910111213

CREATE TABLE `dage` (`id` int(11) NOT NULL auto_increment,`name` varchar(32) default ‘‘,PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=latin1；CREATE TABLE `xiaodi` (`id` int(11) NOT NULL auto_increment,`dage_id` int(11) default NULL,`name` varchar(32) default ‘‘,PRIMARY KEY (`id`),KEY `dage_id` (`dage_id`),CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1；

Insert a Big Brother:

?

123456789

mysql> insert into dage(name) values(‘铜锣湾‘);Query OK, 1 row affected (0.01 sec)mysql> select * from dage;+----+--------+| id | name |+----+--------+| 1 | 铜锣湾 |+----+--------+1 row in set (0.00 sec)

Insert a little brother:

?

12345678

mysql> insert into xiaodi(dage_id,name) values(1,‘铜锣湾_小弟A‘);Query OK, 1 row affected (0.02 sec)mysql> select * from xiaodi;+----+---------+--------------+| id | dage_id | name |+----+---------+--------------+| 1 | 1 | 铜锣湾_小弟A |+----+---------+--------------+

Remove the eldest brother:

?

12	mysql> delete from dage where id=1; error 1451 (23000): Cannot delete or update a parent row:a foreign key constraint fails (' Bstar/xiaodi ', constraint ' xiaodi_ibfk_1 ' foreign key (' dage_id ') references ' dage ' (' Id '))

Hint: No, there is restraint, Big Brother below also have younger brother, can not leave us no matter!

Insert a new little brother:

?

12	mysql> insert into xiaodi(dage_id,name) values(2,‘旺角_小弟A‘); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

Hint: Boy, want to rebel! You don't have a big brother yet!

Increase the event trigger limit for foreign KEY constraints:

?

12345678

mysql> show create table xiaodi;CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1; Query OK, 1 row affected (0.04 sec)Records: 1 Duplicates: 0 Warnings: mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;Query OK, 1 row affected (0.04 sec)Records: 1 Duplicates: 0 Warnings: 0

Try to delete the eldest brother again:

?

123456

mysql> delete from dage where id=1; query OK, 1 row affected (0.01 sec) mysql> select * from dage; empty set (0.01 sec) mysql> select * from xiaodi; empty set (0.00 sec)

Points to note:

MySQL allows the use of foreign keys, but for the purpose of integrity checking, this functionality is ignored in all table types except for the InnoDB table type. This may be weird, but it's actually quite normal: for each insert, update, and delete of all foreign keys for a database, an integrity check is a time-consuming and resource-intensive process that can affect performance, especially when dealing with complex or entangled connection trees.

As a result, users can choose the best combination for specific needs on a table basis. Therefore, if you need better performance and do not need integrity checking, you can choose to use the MyISAM table type, and if you want to build the table in MySQL based on referential integrity and want to maintain good performance on this basis, it is best to choose the table structure as the InnoDB type.

MySQL foreign key usage and explanation