本文知识点概括如下:
外键约束的要求解析:
约束的作用和种类,如下:
列级约束:约束只针对于某一个字段。。
表级约束:约束针对于2个或2个以上的字段。
FOREIGN KEY
外键约束的作用:保持数据的一致性和完整性;实现数据表的一对一或一对多的关系。
外键列:加过FOREIGN KEY关键词的列。
参照列:外键列所参照的那一列。
子表:具有外键列的表。
父表:子表所参照的表。
外键约束的要求,如下:
外键列和参照列必须具有相似的数据类型。若为数字,则数字的 长度和是否有符号位 必须完全一致;
若为字符,则字符的长度可以不同。
外键列和参照列必须创建索引。如果外键列上没有索引的话,MySQL将自动创建索引。
而参照列没有索引的话,MySQL则不会自动创建索引!
编辑数据表的默认存储引擎
打开MySQL的配置文件 my.ini ,修改如下所示的行(将默认的存储引擎修改为INNODB):
[mysqld]
default-storage-engine=INNODB
修改完配置文件,记得重新启动MySQL服务!
mysql> CREATE TABLE provinces( #创建父表(子表所参照的表)
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, # id被定义为主键,而主键列会自动创建索引!
-> pname VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW CREATE TABLE provinces; #可以看到,数据表provinces的存储引擎确实为InnoDB
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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.00 sec)
mysql> CREATE TABLE users( #创建子表(具有外键列的表)
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED, #外键列pid与参照列id的类型都是SMALLINT UNSIGNED,符合要求。
-> FOREIGN KEY (pid) REFERENCES provinces(id) #pid是外键列,数据表provinces中的id是参照列。
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> SHOW INDEXES FROM provinces\G; #显示索引(参照列id被定义为主键,会自动创建索引)
*************************** 1. row ***************************
Table: provinces
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id # 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 users\G; #可以看到,数据表users中,存在2个索引。
*************************** 1. row ***************************
Table: users
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id #第一个索引(数据表users中的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 #第二个索引(如果外键列上没有索引的话,MySQL将自动创建索引。)
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
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`), #id被定义为主键,而主键列会自动创建索引。
KEY `pid` (`pid`), #pid被定义为外键列,系统会为外键列自动添加索引。
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
外键约束的参照操作,如下:
CASCADE使用示例:
mysql> CREATE TABLE users1(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE #父表中的删除或更新操作,同步到子表。
-> );
Query OK, 0 rows affected (0.26 sec)
mysql> SHOW CREATE TABLE users1;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users1 | CREATE TABLE `users1` (
`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 `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | #父表中的删除或更新操作,同步到子表。
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
先在父表中插入记录,然后才能在子表中插入记录!(注意插入记录的顺序)
mysql> INSERT provinces(pname) VALUES('A'); #向父表中插入记录
Query OK, 1 row affected (0.09 sec)
mysql> INSERT provinces(pname) VALUES('B');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT provinces(pname) VALUES('C');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+-------+
3 rows in set (0.00 sec)
mysql> INSERT users1(username,pid) VALUES('Tom',3);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT users1(username,pid) VALUES('John',7);#记录插入失败,因为父表中id=7的记录不存在(插入失败,但占用了一个编号)。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE)
mysql> INSERT users1(username,pid) VALUES('John',1);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT users1(username,pid) VALUES('Rose',3);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM users1; #第2次插入记录时,插入失败。占用了一个编号2。
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 1 | Tom | 3 |
| 3 | John | 1 |
| 4 | Rose | 3 |
+----+----------+------+
3 row