Mysql Day Seven

外键

foreign key：如果一张表中有一个字段指向另一张表的主键，该字段称之为外键；

增加外键

-- 方式1：创建的时候
create table m_foreign(
id int,
child varchar(10),
p_id int,
foreign key(p_id) references m_primary(id)
)charset utf8;

desc m_primary;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | NO   |     | NULL    |       |
| id    | int(11)     | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
desc m_foreign ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| child | varchar(10) | YES  |     | NULL    |       |
| p_id  | int(11)     | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 方式2： 创建之后增加外键
create table m_foreign1(
id int,
child varchar(10),
p_id int
)charset utf8;

alter table m_foreign1 add
-- 指定外键名
constraint m_key
-- 指定外键ziduan
foreign key (p_id)
-- 引用父表主键
references m_primary(id);

desc m_foreign1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| child | varchar(10) | YES  |     | NULL    |       |
| p_id  | int(11)     | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+

删除外键

-- 用法 alter table 表名 drop foreign key 外键名；
-- 删除前
show create table m_foreign ;
+-------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                              |
+-----------+-------------+
| m_foreign | CREATE TABLE `m_foreign` (
  `id` int(11) DEFAULT NULL,
  `child` varchar(10) DEFAULT NULL,
  `p_id` int(11) DEFAULT NULL,
  KEY `p_id` (`p_id`),
  CONSTRAINT `m_foreign_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `m_primary` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+------------+

-- 删除语句
alter table m_foreign drop foreign key m_foreign_ibfk_1;

-- 删除后
show create table m_foreign ;
+-----------+------------+
| Table     | Create Table                                                                                                                                                                          |
+-----------+------------+
| m_foreign | CREATE TABLE `m_foreign` (
  `id` int(11) DEFAULT NULL,
  `child` varchar(10) DEFAULT NULL,
  `p_id` int(11) DEFAULT NULL,
  KEY `p_id` (`p_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+------------+

外键作用

对子表：数据进行写操作时，入过对应的外键在父表中找不到，写入失败

select * from m_primary;
+-------+----+
| name  | id |
+-------+----+
| name1 |  1 |
| name2 |  2 |
+-------+----+

insert into m_foreign1 values (1,"lili",3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`m_foreign1`, CONSTRAINT `m_key` FOREIGN KEY (`p_id`) REFERENCES `m_primary` (`id`))

insert into m_foreign1 values (1,"lili",1);
Query OK, 1 row affected (0.19 sec)

对父表：父表在删除和修改时涉及道主键，如果对应的主键在子表中已经被子表引用，操作失败

select * from m_foreign1;
+------+-------+------+
| id   | child | p_id |
+------+-------+------+
|    1 | lili  |    1 |
+------+-------+------+

select * from m_primary;
+-------+----+
| name  | id |
+-------+----+
| name1 |  1 |
| name2 |  2 |
+-------+----+

update m_primary  set id=3 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`m_foreign1`, CONSTRAINT `m_key` FOREIGN KEY (`p_id`) REFERENCES `m_primary` (`id`))

update m_primary  set id=3 where id=2;
Query OK, 1 row affected (0.16 sec)

外键条件

    1.必须保证表的存储引擎是innodb

    2.外键字段的的字段类型必须和父表的主键类型完全一致

    3.一张表中的外键名字不能重复

    4.子表在存在数据情况下增加外键必须保证子表数据与父表数据对应

外键约束模式

    1.district ：严格模式，父表不能删除或更新被子表已经引用的记录

    2.cascade：级联模式，父表删除，对应子表数据也删除，父表更新，子表也更新

    3.set null：置空模式，父表操作之后子表对应数据置空

    语法：foreign key （字段） references 主表（主键） on delete 模式 on update 模式 （指定父表更新和删除模式）

-- 增加约束
alter table m_foreign add foreign key (p_id) 
references m_primary(id)
on delete set null on update cascade;

select * from m_primary;
+-------+----+
| name  | id |
+-------+----+
| name1 |  1 |
| name2 |  3 |
+-------+----+

select * from m_foreign;
+------+--------+------+
| id   | child  | p_id |
+------+--------+------+
|    1 | lili   |    1 |
|    2 | huahua |    1 |
|    4 | jiji   |    3 |
|    5 | keke   |    3 |
+------+--------+------+

-- 更新
update m_primary set id=2 where id=3;

select * from m_foreign;
+------+--------+------+
| id   | child  | p_id |
+------+--------+------+
|    1 | lili   |    1 |
|    2 | huahua |    1 |
|    4 | jiji   |    2 |
|    5 | keke   |    2 |
+------+--------+------+
4 rows in set (0.00 sec)

-- 删除
delete from  m_primary where id=2;

select * from m_foreign;
+------+--------+------+
| id   | child  | p_id |
+------+--------+------+
|    1 | lili   |    1 |
|    2 | huahua |    1 |
|    4 | jiji   | NULL |
|    5 | keke   | NULL |
+------+--------+------+