To Create a table:#首先登录mysqlmysql-uroot Open a database mysql>use db1;Create a parent table, which we name as province,Mysql> CREATE table province (ID smallint auto_increment key, name varchar () not NULL);Create a child table named student, where its PID reference is from the province ID,Mysql> CREATE TABLE student (ID smallint primary key, name varchar) not NULL, PID Smalli NT, foreign KEY (PID) references province (ID));Note that at this point the type of PID must be consistent with the type of the ID, if it is a number, it should be exactly the same, if the characters, the number of characters can be inconsistent. At this point, the ID is called the reference key. To view automatic indexing:Next, let's look at whether the two tables have been created successfully and their members, such asmysql> show columns in student;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | smallint (6) | NO | PRI | NULL | || name | varchar (10) | NO | | NULL | || pid | smallint (6) | YES | MUL | NULL | |+-------+-------------+------+-----+---------+-------+3 rows in Set (0.00 sec)mysql> Show Columns in province;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | smallint (6) | NO | PRI | NULL | auto_increment || name | varchar (10) | NO | | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in Set (0.00 sec)Take a look at the automatic indexing of two tables,Mysql> Show indexes from province\g;*************************** 1. Row *************************** table:province non_unique:0 key_name:primary seq_in_index:1 column_name:id collation:a& nbsp cardinality:0 sub_part:null packed:null null: index_type:btree comment:i Ndex_comment:1 row in Set (0.00 sec) mysql> Show indexes from student\g;*************************** 1. Row *************************** table:student non_unique:0 key_name:primary seq_in_index:1 column_name:id collation:a& nbsp cardinality:0 sub_part:null packed:null null: index_type:btree Comment:Index_comment :* ************************** 2. Row *************************** table:student non_unique:1 key_name:pid seq_in_index:1 column_name:pid collation:a cardinality:0 sub_part:null packed:null null:yes index_type:btree Comment:index_comment:2 rows in Set (0.00 sec) Thus, we see that the PID in the student table has also created an automatic index.
referential actions for foreign KEY constraintsforeign KEY constraints mainly have the following major categories:Cascade: Deletes or updates from the parent table and automatically deletes or updates the matching content in the Word table;set NULL: Deleted or updated from the parent table and the matching content in the child table is set to NULL, use this feature to ensure that the Word table column is not specified as NOT null;Restrict: The deletion or update of the parent table is denied;NO action: Standard SQL keyword, same as restrict in MySQL;Next, create a new table to illustrate the situation, first delete the previous student table, such asmysql> drop table student; Query OK, 0 rows affected (0.13 sec) mysql> show tables;+-----------------------+| tables_in_zzw_test_db |+-----------------------+| Province | | TB1 |+-----------------------+2 rows in Set (0.00 sec)Next, recreate a student table and specify that the FOREIGN KEY constraint is cascade,Mysql> CREATE TABLE student (ID smallint primary key, name varchar) not NULL, PID small int, foreign KEY (PID) references province (ID) on DELETE cascade); Query OK, 0 rows affected (0.33 sec)Next, add data to the province,mysql> Insert Province (name) values ("A"); Query OK, 1 row affected (0.05 sec) mysql> Insert Province (name) values ("B"); Query OK, 1 row affected (0.06 sec) mysql> Insert Province (name) values ("C"); Query OK, 1 row affected (0.04 sec) mysql> Insert Province (name) values ("D"); Query OK, 1 row affected (0.03 sec) mysql> select * FROM province;+----+------+| ID | Name |+----+------+| 1 | A | | 2 | B | | 3 | C | | 4 | D |+----+------+4 rows in Set (0.00 sec)Next, add data to the student, such asMysql> Insert student VALUES (1234, "Tom", 1); Query OK, 1 row affected (0.06 sec) mysql> Insert student values (1235, "John", 2); Query OK, 1 row affected (0.03 sec) mysql> Insert student values (1236, "Mary", 3); Query OK, 1 row affected (0.04 sec) mysql> SELECT * FROM student;+------+------+------+| ID | name | PID |+------+------+------+| 1234 | Tom | 1 | | 1235 | John | 2 | | 1236 | Mary | 3 |+------+------+------+3 rows in Set (0.00 sec)now, to remove the line with ID 3 in province,Mysql> Delete from province where id=3; Query OK, 1 row affected (0.04 sec) mysql> Select * FROM province;+----+------+| ID | Name |+----+------+| 1 | A | | 2 | B | | 4 | D |+----+------+3 rows in Set (0.00 sec)now to see if the data in the Sub-table student has been affected after the deletion ,Mysql> SELECT * FROM student;+------+------+------+| ID | name | PID |+------+------+------+| 1234 | Tom | 1 | | 1235 | John | 2 |+------+------+------+2 rows in Set (0.01 sec)we have found that pid=3 's line is no longer there, that it is also automatically deleted, and this is what we have on DELETE cascade. (Note that the FOREIGN KEY constraint requires the default engine to be the InnoDB engine, and the default engine can be modified by modifying the configuration file/usr/share/mysql/my-default.cnf in the Linux environment)
FOREIGN KEY constraints for MySQL
