• 中文
    • English
  • 注册
  • 查看作者
  • 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
  • 5.5k
  • 请登录之后再进行评论

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