MySQL foreign key usage and description

MySQL foreign key usage and description

I. Foreign key constraints

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

Usage conditions of foreign keys:

1. The two tables must be InnoDB tables, and MyISAM tables do not currently support foreign keys (it is said that foreign keys may be supported in later versions, but at least not supported currently );

2. The foreign key column must have an index. MySQL 4.1.2 and later versions will automatically create an index when creating the foreign key. However, if the foreign key column is in an earlier version, the index must be created;

3. the columns of the foreign key relationship must be of similar data types, that is, columns that can be converted to each other. For example, int and tinyint can be used, but int and char cannot;

Benefits of Foreign keys:

Two tables can be associated to ensure data consistency and perform cascade operations;

Syntax for defining foreign keys:

[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 in create table and alter table. If the CONSTRAINT symbol is not specified, MYSQL automatically generates a name.

On delete and on update indicate the event trigger limit. You can set the following parameters:

RESTRICT)
CASCADE (with foreign key changes)
Set null (set null)
SET DEFAULT)
No action (no action, default)

This is a simple demonstration of using dage and xiaodi tables. The eldest brother table is the primary key and the younger brother table is the foreign key.

Table creation:

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 eldest brother:

Mysql> insert into dage (name) values ('causeway Bay '); Query OK, 1 row affected (0.01 sec) mysql> select * from dage; + ---- + -------- + | id | name | + ---- + -------- + | 1 | Causeway Bay | + ---- + -------- + 1 row in set (0.00 sec)

Insert a younger brother:

Mysql> insert into xiaodi (dage_id, name) values (1, 'causeway Bay _ A A'); Query OK, 1 row affected (0.02 sec) mysql> select * from xiaodi; + ---- + --------- + -------------- + | id | dage_id | name | + ---- + --------- + -------------- + | 1 | 1 | Causeway Bay _ younger brother A | + ---- + --------- + ---------------- +

Delete the eldest brother:

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`))

Tip: No, there are constraints. You can leave us alone if you have a younger brother or younger brother!

Insert a new younger brother:

Mysql> insert into xiaodi (dage_id, name) values (2, 'mong Kok _ 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 '))

Tip: I want to fight! You have no big brother yet!

Add the event trigger limit to the foreign key constraint:

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 again to delete the eldest brother:

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) 

Note:

MySQL allows foreign keys, but this function is ignored in all table types except InnoDB table types for the purpose of integrity test. This may be a bit weird, but it is actually quite normal: It is time-consuming and resource-consuming to perform the integrity check for each insertion, update, and deletion of all foreign keys in the database, it may affect performance, especially when dealing with complex or winding connection trees.

Therefore, you can select the best combination suitable for specific needs based on the table. Therefore, if you need better performance and do not need integrity check, you can select the MyISAM Table type, if you want to create a table in MySQL Based on the integrity of the reference and maintain good performance on this basis, it is best to select the table structure as innoDB type.

The above section describes how to use and describe MySQL Foreign keys. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!