Mysql foreign key use and explain the detailed _mysql

First, FOREIGN KEY constraints

MySQL uses foreign KEY constraints to guarantee the integrity and accuracy of data between tables.

Use conditions for foreign keys:

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

2. The foreign key column must be indexed, and the MySQL 4.1.2 version will automatically create the index when the foreign key is established, but if it is required to be displayed in an earlier version;

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, while int and char are not;

Benefits of foreign keys:

The two tables can be correlated to ensure the consistency of the data and realize some cascade operation;

Definition syntax for 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, and a name is automatically generated if constraint symbol,mysql is not specified.

On DELETE and on update to indicate the event trigger limit, you can set parameters:

RESTRICT (restricts foreign key changes in the appearance)
CASCADE (following foreign key changes)
Set NULL (SET NULL value)
Set default (Set defaults)
No action (no action, default)

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

Build table:

CREATE TABLE ' dage ' (
' id ' int () not NULL auto_increment,
' name ' varchar () default ',
PRIMARY KEY (' id '))
engine=innodb DEFAULT charset=latin1;
CREATE TABLE ' Xiaodi ' (
' id ' int () NOT NULL auto_increment,
' dage_id ' int (one) default null,
' name ' varchar ( "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:

mysql> INSERT into Dage (name) VALUES (' Tongluowan ');
Query OK, 1 row affected (0.01 sec)
mysql> select * from Dage;
+----+--------+
| id | name |
+----+--------+
| 1 | Tongluowan
| +----+--------+
1 row in Set (0.00 sec)

Insert a little brother:

mysql> INSERT into Xiaodi (dage_id,name) VALUES (1, ' Tongluowan _ brother a ');
Query OK, 1 row affected (0.02 sec)
mysql> select * from Xiaodi;
+----+---------+--------------+
| id | dage_id | name |
+----+---------+--------------+
| 1 | 1 | Tongluowan _ 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 ' Xiao Di_ibfk_1 ' FOREIGN KEY (' dage_id ') REFERENCES ' dage ' (' ID ')

Tip: No, there are constraints, brother under the younger brother, can not leave us no matter!

Insert a new little brother:

mysql> INSERT into Xiaodi (Dage_id,name) VALUES (2, ' Mong Kok _ brother a '); 
ERROR 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails (' Bstar/xiaodi ', constraint ' xiaodi_i Bfk_1 ' FOREIGN KEY (' dage_id ') REFERENCES ' dage ' (' ID ')

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

Add a FOREIGN KEY constraint to the event trigger limit:

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 Y (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:

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;

You need to be aware of the point:

MySQL allows the use of foreign keys, but for the purposes of integrity testing, this feature is ignored in all table types except InnoDB table types. This may be weird, but it's actually quite normal: integrity checking after every insert, update, and delete of all foreign keys to a database is a time-consuming and resource process that can affect performance, especially when dealing with complex or tangled connection trees.

As a result, users can choose the best combination for specific needs on the basis of a table. So, if you need better performance and do not require 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.

The above is a small set to introduce the MySQL foreign key use and explanation, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!