MySQL外键约束的实例讲解
MySQL的外键约束是用来在两个表之间建立链接的,其中一个表发生变化,另外一个表也发生变化。从这个特点来看,它主要是为了保证表数据的一致性和完整性的。
对于两个通过外键关联的表,相关联字段中主键所在的表是主表,也称之为父表,外键所在的表是从表,也称之为子表,定义外键的时候需要遵守几个规则:
1、父表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则父表与子表是同一个表,这样的表称为自参照表,这种结构称为自参照。
2、必须为父表定义主键。
3、主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
4、外键中列的数目必须和父表的主键中列的数目相同。
5、外键中列的数据类型必须和父表主键中对应列的数据类型相同。说这么多比较笼统,还是看看例子吧。
mysql:yeyztest ::>>create table fk_test_1( -> id int not null primary key auto_increment, -> name varchar() default '');Query OK, rows affected (0.10 sec)mysql:yeyztest ::>>create table fk_test_2( -> id int not null primary key auto_increment, -> uid int, -> foreign key fk_uid(uid) references fk_test_1(id));Query OK, rows affected (0.06 sec)
mysql:yeyztest ::>>insert into fk_test_1 values (,'aaa'),(,'bbb');Query OK, rows affected (0.00 sec)Records: Duplicates: Warnings: mysql:yeyztest ::>>select * from fk_test_1;+----+------+| id | name |+----+------+| | aaa || | bbb |+----+------+ rows in set (0.00 sec)mysql:yeyztest ::>>insert into fk_test_2 values (,),(,);Query OK, rows affected (0.00 sec)Records: Duplicates: Warnings: mysql:yeyztest ::>>insert into fk_test_2 values (,);Query OK, row affected (0.00 sec)mysql:yeyztest ::>>insert into fk_test_2 values (,); ERROR (): Cannot add or update a child row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))
再来看看删除的情况,
mysql:yeyztest ::>>select * from fk_test_2 ;+----+------+| id | uid |+----+------+| | || | || | |+----+------+ rows in set (0.00 sec)mysql:yeyztest ::>>delete from fk_test_2 where id=;Query OK, row affected (0.00 sec)mysql:yeyztest ::>>select * from fk_test_1 ; +----+------+| id | name |+----+------+| | aaa || | bbb |+----+------+ rows in set (0.00 sec)mysql:yeyztest ::>>delete from fk_test_1 where id=; ERROR (): Cannot delete or update a parent row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))
既然delete不成功,试试update,
mysql:yeyztest ::>>update fk_test_1 set id= where id=; ERROR (): Cannot delete or update a parent row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))mysql:yeyztest ::>>update fk_test_1 set name='ccc' where id=; Query OK, row affected (0.00 sec)Rows matched: Changed: Warnings:
到这里,我们已经知道,外键的存在是为了保证数据的完整和统一性,但是也带来了一点问题,那就是父表中凡是被子表依赖的列,都没办法删除了,这不是我们想要的,有一些数据确实会过期,我们有删除的需求,那么这个时候应该怎么办?
在上面的测试中,我们反复提到一个词,就是默认情况,我们没有设置外键的删除和更新规则,这里mysql帮我们使用了最严格的的规则,那就是restrict,其实还有其他一些规则,这里全部列出来:
- delete父表的情况:
cascade,set null,no action,restrict
- update父表的情况:
cascade,set null,no action,restrict
其中
- restrict是默认操作,它表示拒绝父表删除或者修改外键已经被子表所依赖的列,这是最安全的设置;
- cascade表示在父表发生删除的时候直接删除子表的记录,这是最危险的设置;
- set null表示父表删除的时候,对子表进行null值处理;
- no action表示父表删除的时候,子表不进行任何改动。
设置关联的语法如下:
alter table 表名 add constraint FK_ID foreign key (外键字段名) references 外表表名 (主键字段名)[on delete {cascade | set null | no action| restrict}][on update {cascade | set null | no action| restrict}]
mysql:yeyztest ::>>select * from fk_test_1;+----+------+| id | name |+----+------+| | ccc || | bbb |+----+------+ rows in set (0.00 sec)mysql:yeyztest ::>>select * from fk_test_2;+----+------+| id | uid |+----+------+| | || | |+----+------+ rows in set (0.00 sec)mysql:yeyztest ::>>show create table fk_test_2\G*************************** 1. row *************************** Table: fk_test_2Create Table: CREATE TABLE `fk_test_2` ( `id` int() NOT NULL AUTO_INCREMENT, `uid` int() DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_uid` (`uid`), CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`)) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8 row in set (0.00 sec)mysql:yeyztest ::>>alter table fk_test_2 drop foreign key fk_test_2_ibfk_1;Query OK, rows affected (0.02 sec)Records: Duplicates: Warnings: mysql:yeyztest ::>>alter table fk_test_2 add constraint fk_uid foreign key (uid) references fk_test_1(id) on delete cascade;Query OK, rows affected (0.03 sec)Records: Duplicates: Warnings: ###########################################此处删除父表id=的记录,查看子表的结果##########################################mysql:yeyztest ::>>delete from fk_test_1 where id=;Query OK, row affected (0.00 sec)mysql:yeyztest ::>>select * from fk_test_1 ;+----+------+| id | name |+----+------+| | ccc |+----+------+ row in set (0.00 sec)mysql:yeyztest ::>>select * from fk_test_2 ;+----+------+| id | uid |+----+------+| | |+----+------+ row in set (0.00 sec)
在看一眼set null的情况:
mysql:yeyztest ::>>alter table fk_test_2 drop foreign key fk_uid; Query OK, row affected (0.02 sec)Records: Duplicates: Warnings: mysql:yeyztest ::>>alter table fk_test_2 add CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`) ON DELETE set null;Query OK, row affected (0.03 sec)Records: Duplicates: Warnings: mysql:yeyztest ::>>delete from fk_test_1 where id=;Query OK, row affected (0.00 sec)mysql:yeyztest ::>>select *from fk_test_1 ;Empty set (0.00 sec)mysql:yeyztest ::>>select *from fk_test_2 ;+----+------+| id | uid |+----+------+| | NULL |+----+------+ row in set (0.00 sec)
no action的情况也是类似,只不过是子表的记录没有发生任何改动。
以上是父表进行delete的操作,当父表进行update的时候,子表可以选择的情况也有以上四种,和delete基本保持一致,这里不再赘述。有兴趣可以自己测试一发。
最后,说明一点,子表的外键列可以为空值。
mysql:yeyztest ::>>insert into fk_test_1 values (,);Query OK, row affected (0.00 sec)mysql:yeyztest ::>>select *from fk_test_2 ; +----+------+| id | uid |+----+------+| | NULL |+----+------+ row in set (0.00 sec)mysql:yeyztest ::>>insert into fk_test_2 values (,NULL);Query OK, row affected (0.00 sec)mysql:yeyztest ::>>insert into fk_test_2 values (,NULL);Query OK, row affected (0.00 sec)mysql:yeyztest ::>>select * from fk_test_2;+----+------+| id | uid |+----+------+| | NULL || | NULL || | NULL |+----+------+ rows in set (0.00 sec)
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Linux下MYSQL 5.7 找回root密码的问题(亲测可用)
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- 《Android和PHP最佳实践》官方站
- android用户界面之按钮(Button)教程实例汇
- TabHost与RadioGroup结合完成的菜单【带效果图】5个Activity
- Android(安卓)UI开发第十七篇——Android(安卓)Fragment实例(Lis
- Android——Activity四种启动模式
- Android布局(序章)