一. 约束意义和分类
约束保证数据的完整性和一致性。
约束分为表级约束和列级约束。
二. 约束类型:
约束类型包括:
- 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为外键
请登录之后再进行评论