• 注册
  • 赞助本站

    • 支付宝
    • 微信
    • QQ

    感谢一直支持本站的所有人!

    • 查看作者
    • 3-2:MySQL 外键约束的要求解析

      一.  约束意义和分类

      约束保证数据的完整性和一致性。

      约束分为表级约束和列级约束。

      二.  约束类型:

      约束类型包括:

      • NOT NULL(非空约束)
      • PRIMARY KEY(主键约束)
      • UNIQUE KEY(唯一约束)
      • DEFAULT(默认约束)
      • FOREIGN KEY(外键约束)

      三.  FOREIGN KEY(外键约束)

      外键约束是为了保持数据一致性和完整性

      实现一对一或者一对多的关系

      四.  外键约束的要求

      • 父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
      • 数据表的存储引擎只能为InnoDB
      • 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同,而字符的长度则可以不同。
      • 外键列和参照列必须创建索引|。如果外键列不存在索引的话,MySQL将自动创建索引。

      五.  编辑数据表的默认存储引擎

      打开my.ini文件,将default-storage-engine=设置为INNODB(一般默认就是INNODB),如下:

      default-storage-engine=INNODB

      六.  外键和参照列的数据类型

      首先创建一个父表:省份数据表:

      将id字段设置主键约束(PRIMARY KEY)和自动编号(AUTO_INCREMENT)

      pname字段为省份名字

      mysql> CREATE TABLE provinces(
          -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
          -> pname VARCHAR(20) NOT NULL
          -> );

      查看该表的存储引擎,可以用SHOW CREATE TABLE命令显示用于创建给定表的CREATE TABLE语句:

      mysql> SHOW CREATE TABLE provinces;
      +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table     | Create Table                                                                                                                                                              |
      +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | provinces | CREATE TABLE `provinces` (
        `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
        `pname` varchar(20) NOT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
      +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.01 sec)

      可以看到存储引擎为InnoDB

      接来下再创建一个子表:用户表:

      pid是用户的省份,这里不需要单独再加一个字符型的省份字段,因为是关系型数据库,这里只需要存储省份的编号即可

      pid也就是外键列,将参照主键表也就是父表中的id字段,id为参照列

      mysql> CREATE TABLE users(
          -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
          -> username VARCHAR(10) NOT NULL,
          -> pid BIGINT,
          -> FOREIGN KEY(pid) REFERENCES provinces(id)
          -> );
      ERROR 1005 (HY000): Can't create table 'test.users' (errno: 150)

      可以看到,我们将pid设置为BIGINT,因为外键列和参照列必须具有相似的数据类型,所以这里会报错。

      接下来我们将pid设置为id类型设置为一致类型:

      mysql> CREATE TABLE users(
          -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
          -> username VARCHAR(10) NOT NULL,
          -> pid SMALLINT,
          -> FOREIGN KEY(pid) REFERENCES provinces(id)
          -> );
      ERROR 1005 (HY000): Can't create table 'test.users' (errno: 150)

      发现依旧报错,这是为什么呢?其实是因为在父表中,id的数据类型不光是SMALLINT,还是UNSIGNED,所以pid也必须为无符位才可以

      mysql> CREATE TABLE users(
          -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
          -> username VARCHAR(10) NOT NULL,
          -> pid SMALLINT UNSIGNED,
          -> FOREIGN KEY(pid) REFERENCES provinces(id)
          -> );
      Query OK, 0 rows affected (0.01 sec)

      创建成功!接下来看一下外间列和参照列是否创建了索引呢?

      首先查看参照列id,我们并没为参照列创建索引,那么参照列是否自动创建了索引呢?答案是肯定的,因为id是主键,而在MySQL中,当你建立主键时,索引同时也已建立起来了,我们通过

      show index from table_name;

      命令来显示表的索引:

      mysql> SHOW INDEXES FROM provinces;
      +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | provinces |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
      +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      1 row in set (0.01 sec)

      在SHOW命令后添加\G(这里值得注意的是,教程中老师习惯性的在\G后面加上;这样会导致No query specified错误,其实\G后面不需要加;)便于查看:

      mysql> SHOW INDEXES FROM provinces \G
      *************************** 1. row ***************************
              Table: provinces
         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)

      可以看到id字段已经创建了索引

      再来看一下外间列:

      mysql> SHOW INDEXES FROM users \G;
      *************************** 1. row ***************************
              Table: users
         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: users
         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)
      ERROR:
      No query specified

      可以看到users表中,一共有两个索引:主键索引和pid索引(pid索引是系统自动创建)

      再查看一下users表:

      mysql> SHOW CREATE TABLE users;
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                                                                                                            |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | users | CREATE TABLE `users` (
        `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
        `username` varchar(10) NOT NULL,
        `pid` smallint(5) unsigned DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `pid` (`pid`),
        CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
      +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      可以看到FOREIGN KEY (`pid`)说明pid为外键

    • 0
    • 2
    • 0
    • 3.5k
    • 请登录之后再进行评论

      登录
    • 0
      张甲46站长
      @geniuspig 谢谢支持,有不好的地方欢迎批评指导
    • 0
      笔记不错,省得我自己做了。
    • 单栏布局 侧栏位置: