MySQL DDL Operation--------FOREIGN key best combat

1. Background

* MySQL has two commonly used engine types MyISAM and InnoDB. Currently only the InnoDB engine type supports foreign key constraints.

* The columns of this table must be the same as the foreign key type, the foreign key must be the primary key of the appearance

* Set the built-in column cannot set the No NULL field property.

2. The role of external construction

* make two tables associative, foreign keys can only refer to the values of columns in the outside

* maintain data consistency, integrity, control of data stored in foreign key tables

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9A/8E/wKiom1lXWFahYAJAAADRovi0L_w011.png "title=" FOREIGN key. png "alt=" Wkiom1lxwfahyajaaadrovi0l_w011.png "/>

3. Foreign key Experiment [ employees--departments ]

* create foreign key-dependent appearance departments

Mysql> CREATE TABLE departments (ID BIGINT PRIMARY KEY not NULL auto_increment, name VARCHAR NULL) Engine=innodb charset=utf8mb4; Query OK, 0 rows affected (0.05 sec)

* Create an employee table empoyees and establish a foreign key

Specify the FOREIGN key keyword: FOREIGN KEY (column name)

reference FOREIGN key keyword: REFERENCES < foreign key table name > (foreign key column name)

Event Trigger limit: [default] no action

On delete set NULL when the outer field is deleted, this table is set to null (null value) [recommend setting this recommendation]

On update CASCADE the FOREIGN key cascade update for the table when the outer field is updated

On Delete/update set default when an event is triggered

On Delete/update RESTRICT to restrict foreign key changes in appearances when there is an event trigger

Mysql> CREATE TABLE empoyees (id BIGINT PRIMARY KEY not NULL auto_increment, "Sex ENUM" (' M ', ' F ') not NULL, age INT not NULL, department BIGINT, FOREIGN KEY (department), REFERENCES Departm Ents (ID), on DELETE SET NULL, on UPDATE CASCADE, Engine=innodb charset=utf8mb4; Query OK, 0 rows affected (0.12 sec)

* Insert data to Department table departments

Mysql> INSERT into Departments SELECT NULL, ' Dev '; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0mysql> INSERT into departments SELECT NULL, ' t EST '; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0mysql> INSERT into departments SELECT NULL, ' O PS '; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0

* View Departmental table data

Mysql> SELECT * FROM departments;+----+------+| ID |  Name |+----+------+| 1 |  dev | | 2 |  Test | | 3 | OPS |+----+------+3 rows in Set (0.00 sec)

* Insert Normal data (department number exists in department table)

Mysql> INSERT into Empoyees SELECT NULL, ' M ', 22, 2; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0mysql> SELECT * from Empyees; ERROR 1146 (42S02): Table ' mytest.empyees ' doesn ' t existmysql> SELECT * FROM empoyees;+----+-----+-----+------------+ | ID | sex | Age |  Department |+----+-----+-----+------------+| 1 |  M |          22 | 2 |+----+-----+-----+------------+1 row in Set (0.01 sec)

* Insert unhealthy data (the department number does not exist in the Department table) [ column with ID 4 does not exist in the departmental table ]

Mysql> INSERT into Empoyees SELECT NULL, ' M ', 22, 4; ERROR 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails (' mytest '. ' Empoyees ', constraint ' emp Oyees_ibfk_1 ' FOREIGN KEY (' Department ') REFERENCES ' departments ' (' ID ') on DELETE SET NULL on UPDATE CASCADE)

   *  Modify the Department table data [employee table has data associated with the Department table ID 2]

mysql> select * from empoyees;+----+-----+-----+------------+| id | sex  | age | department |+----+-----+-----+------------+|  1 | m    |  22 |          2 |+- ---+-----+-----+------------+1 row in set  (0.01 sec) mysql> update  departments set id = 4 where id=2; query ok, 1 row affected  (0.04 sec) rows matched: 1  changed:  1  warnings: 0mysql> select * from empoyees;+----+-----+-----+---- --------+| id | sex | age | department |+----+-----+-----+------------ +|  1 | m   |  22 |           4 |+----+-----+-----+------------+1 row in set  (0.01 SEC) 

* Delete departmental table data [employee table has data associated with the Department table ID 4]

mysql> select * from empoyees;+----+-----+-----+-- ----------+| id | sex | age | department |+----+-----+-----+---------- --+|  1 | m   |  22 |           2 |+----+-----+-----+------------+1 row in set  (0.01 sec ) mysql> delete from departments where id = 4; query ok, 1 row affected  (0.01 sec) mysql> select * from  empoyees;+----+-----+-----+------------+| id | sex | age | department  |+----+-----+-----+------------+|  1 | m   |  22 |        null |+----+-----+-----+------------+1 row in set   (0.00 SEC) 

4. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1943689

MySQL DDL Operation--------FOREIGN key best combat