LinuxEye - Linux系统教程

LinuxEye - Linux系统教程

当前位置: 主页 > 数据库 >

详解MySQL的外键约束

时间:2015-07-31 09:28来源:未知 编辑:linuxeye 点击:
创建表格 首先登录mysql,如 mysql -uroot -ppassword 打开一个数据库,如 mysqluse db1; 创建一个父表,我们命名为province,如 mysql create table province ( - id smallint auto_increment key, - name varchar(10) NOT
创建表格
首先登录mysql,如
mysql -uroot -ppassword
打开一个数据库,如
mysql>use db1;
创建一个父表,我们命名为province,如
mysql> create table province (
    -> id smallint auto_increment key,
    -> name varchar(10) NOT NULL
    -> );
创建一个子表,命名为student,其中其pid引用来自province的id,如
mysql> create table student (
    -> id smallint primary key,
    -> name varchar(10) NOT NULL,
    -> pid smallint,
    -> foreign key(pid) references province(id)
    -> );
注意,此时pid的类型一定要与id的类型一致,若为数字,则要完全一致;若为字符,字符个数可以不一致。此时,id称为参照键。

查看自动索引
接下来,我们查看一下两个表是否已经创建成功及其成员,如
mysql> 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)

再看一下两个表的自动索引情况,如
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
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_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
  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)
因而,我们看到student表中pid也已经创建了自动索引了。


外键约束的参照操作
外键约束主要有以下几大类:
cascade:从父表删除或更新且自动删除或更新字表中的匹配的内容;
set null:从父表删除或更新且子表中相匹配的内容设置为NULL,使用此功能必须保证字表列没有指定为NOT NULL;
restrict:拒绝对父表的删除或更新操作;
no action:标准SQL关键字,在mysql中与restrict相同;


下面再创建一个新表来说明情况,先删除之前的student表,如
mysql> 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)
接下来,重新创建一个student表,并且指定外键约束为cascade,如
mysql>  create table student (                                                                                                                           
    -> id smallint primary key,
    -> name varchar(10) NOT NULL,
    -> pid smallint,
    -> foreign key(pid) references province(id) on delete cascade
    -> );
Query OK, 0 rows affected (0.33 sec)
接着,向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)
接着,向student中添加数据,如
mysql> 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)
现在,要把province中id为3的一行删除掉,如
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)
现在来查看删除过后,子表student中的数据是否受到了影响,如
mysql> select * from student;
+------+------+------+
| id   | name | pid  |
+------+------+------+
| 1234 | Tom  |    1 |
| 1235 | John |    2 |
+------+------+------+
2 rows in set (0.01 sec)
我们,发现pid=3的一行已经不存在了,即它也自动删除了,这就是我们on delete cascade的作用。

(注意,外键约束需要默认引擎为innodb引擎,linux环境下可以通过修改配置文件/usr/share/mysql/my-default.cnf来修改默认引擎)

转载请保留固定链接: https://linuxeye.com/database/2758.html

------分隔线----------------------------
标签:mysql外键约束
栏目列表
推荐内容