【linux】循序渐进学运维-MySQL-外键约束

外键约束 什么是外键约束 MySQL的外键约束是用来在两个表之间建立链接的，其中一个表发生变化，另外一个表也发生变化。从这个特点来看，它主要是为了保证表数据的一致性和完整性的。

对于两个通过外键关联的表，相关联字段中主键所在的表是主表，也称之为父表，外键所在的表是从表，也称之为子表，定义外键的时候需要遵守几个规则：

1、父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照。 2、必须为父表定义主键。 3、主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。 4、外键中列的数目必须和父表的主键中列的数目相同。

举例： 有二张表，一张是用户表，一张是订单表 如果两张表没有关联，会出现两种情况：

如果我删除了用户表里的用户，那么订单表里面与这个用户有关的数据，就成了无头数据了，不完整了。如果我在订单表里面，随便插入了一条数据，这个订单在用户表里面，没有与之对应的用户。这样数据也不完整了。 所以我们要引入外键这个概念 上面的两张表有外键的话，就方便多了，可以不让用户删除数据，或者删除用户的话，通过外键同样删除订单表里面的数据，这样也能让数据完整。

外键约束的语法：

创建外键约束 外键： 每次插入或更新时，都会检查数据的完整性。

方法一：通过create table创建外键 语法：

create table 数据表名称( [CONSTRAINT [约束名称]] FOREIGN KEY [外键字段] REFERENCES [外键表名](外键字段，外键字段2…..) [ON DELETE CASCADE ] [ON UPDATE CASCADE ] )

关于参数的解释： RESTRICT: 拒绝对父表的删除或更新操作。 CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用 注意： on update cascade是级联更新的意思， on delete cascade是级联删除的意思， 意思就是说当你更新或删除主键表，那外键表也会跟随一起更新或删除。

精简化后的语法：

语法：foreign key 当前表的字段 references 外部表名 （关联的字段） ENGINE =innodb

注：创建成功，必须满足以下4个条件：

确保参照的表和字段存在。组成外键的字段被索引。必须使用ENGINE指定存储引擎为：innodb。外键字段和关联字段，数据类型必须一致。

举例： 我们创建一个数据库，包含用户信息表和订单表

mysql> create table `user`(id int(11) not null auto_increment,name varchar(50) not null default '', sex int(1) not null default '0', primary key(id))ENGINE=innodb; Query OK, 0 rows affected (0.02 sec)

需要注意的是：创建时，如果表名是sql关键字，使用时，需要使用反引号`` 创建订单表

mysql> create table `order1`(order_id int(11) auto_increment, u_id int(11) default '0', username varchar(40),money int(11),primary key(order_id),index(u_id),foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) type=innodb;

Query OK, 0 rows affected (0.00 sec)

注： on delete cascade on update cascade 添加级联删除和更新。 确保参照的表user中id字段存在。 组成外键的字段u_id被索引。 必须使用type指定存储引擎为：innodb。

外键字段和关联字段，数据类型必须一致。 插入测试数据

mysql> insert into `user`(name,sex)values('zr',2),('wld',1),('zmedu',1); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into `order1`(u_id,username,money)values(1,'zr',10000),(1,'wld',13000),(3,'ljc',25000); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from `order1`; +------+------+----------+-------+ | o_id | u_id | username | money | +------+------+----------+-------+ | 1 | 1 | zr | 10000 | | 2 | 1 | wld | 13000 | | 3 | 3 | ljc | 25000 | +------+------+----------+-------+ 3 rows in set (0.00 sec) mysql> select * from `user`; +----+-------+-----+ | id | name | sex | +----+-------+-----+ | 1 | zr | 2 | | 2 | wld | 1 | | 3 | zmedu | 1 | +----+-------+-----+ 3 rows in set (0.00 sec) mysql> select id,name,sex,money, o_id from `user`,`order1` where id=u_id; +----+-------+-----+-------+------+ | id | name | sex | money | o_id | +----+-------+-----+-------+------+ | 1 | zr | 2 | 10000 | 1 | | 1 | zr | 2 | 13000 | 2 | | 3 | zmedu | 1 | 25000 | 3 | +----+-------+-----+-------+------+ 3 rows in set (0.00 sec)

测试级联删除： 我们删除用户id1， 查看order1中id为1用户是否被删除

mysql> delete from user where id =1; Query OK, 1 row affected (0.00 sec) mysql> select * from `order1`; +----------+------+----------+-------+ | order_id | u_id | username | money | +----------+------+----------+-------+ | 3 | 3 | ljc | 25000 | +----------+------+----------+-------+ 1 row in set (0.00 sec)

测试级联更新： 更新前数据状态

mysql> select * from `order1`; +----------+------+----------+-------+ | order_id | u_id | username | money | +----------+------+----------+-------+ | 3 | 3 | ljc | 25000 | +----------+------+----------+-------+ 1 row in set (0.00 sec) mysql> select * from user; +----+-------+-----+ | id | name | sex | +----+-------+-----+ | 2 | wld | 1 | | 3 | zmedu | 1 | +----+-------+-----+ 2 rows in set (0.00 sec) mysql> update user set id=5 where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +----+-------+-----+ | id | name | sex | +----+-------+-----+ | 2 | wld | 1 | | 5 | zmedu | 1 | +----+-------+-----+ 2 rows in set (0.00 sec)

方法二：通过alter table 创建外键和级联更新，级联删除 语法：

alter table 数据表名称 add [constraint [约束名称] ] foreign key (外键字段,..) references 数据表(参照字段,...) [on update cascade|set null|no action] [on delete cascade|set null|no action] )

14.4.3 删除外键 语法 alter table 数据表名称 drop foreign key 约束（外键）名称 mysql> alter table order1 drop foreign key order1_ibfk_1; mysql> show create table order1;

初始order1，查看 show create table order1; +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | order1 | CREATE TABLE `order1` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT '0', `username` varchar(40) DEFAULT NULL, `money` int(11) DEFAULT NULL, PRIMARY KEY (`order_id`), KEY `u_id` (`u_id`), CONSTRAINT `order1_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table order1 drop foreign key order1_ibfk_1; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table order1; +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | order1 | CREATE TABLE `order1` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT '0', `username` varchar(40) DEFAULT NULL, `money` int(11) DEFAULT NULL, PRIMARY KEY (`order_id`), KEY `u_id` (`u_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | 1 row in set (0.00 sec)