MySQL中的外键（foreign key）

爱被打了一巴掌 2022-10-11 15:59 380阅读 0赞

引言  
在MySQL中，我们都对主键比较了解，知道主键的主要作用是唯一区分表中的各个行；但是，对于外键（foreign key） 比较陌生。那么什么是外键呢？外键的作用是什么呢？

### 一、外键、外键作用及其限制条件 ###

1.外键的定义：  
外键是某个表中的一列，它包含在另一个表的主键中。  
外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。  
一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。  
2.外键的作用：  
外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。主要体现在以下两个方面：

阻止执行

从表插入新行，其外键值不是主表的主键值便阻止插入。  
从表修改外键值，新值不是主表的主键值便阻止修改。  
主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)。  
主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。  
级联执行

主表删除行，连带从表的相关行一起删除。  
主表修改主键值，连带从表相关行的外键值一起修改。  
3.外键创建限制  
父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。  
必须为父表定义主键。  
外键中列的数目必须和父表的主键中列的数目相同。  
两个表必须是InnoDB表，MyISAM表暂时不支持外键。  
外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立。  
外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

###    二、外键创建方法 ###

可以在创建表时创建外键，也可以在已有的表中增加外键。我们主要讲第二种方式创建外键。

1.创建外键的语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
    REFERENCES 外表表名(主键字段名)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

其中，ON DELETE 和 ON UPDATE表示事件触发限制，各参数意义如下：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <th>参数</th> 
   <th>意义</th> 
  </tr> 
  <tr> 
   <td>RESTRICT</td> 
   <td>限制外表中的外键改动（默认值，也是最安全的设置）</td> 
  </tr> 
  <tr> 
   <td>CASCADE</td> 
   <td>跟随外键改动</td> 
  </tr> 
  <tr> 
   <td>SET NULL</td> 
   <td>设为null值</td> 
  </tr> 
  <tr> 
   <td>NO ACTION</td> 
   <td>无动作</td> 
  </tr> 
  <tr> 
   <td>SET DEFAULT</td> 
   <td>设为默认值</td> 
  </tr> 
 </tbody> 
</table>

2.举例  
**（1）创建两张表：**

CREATE TABLE student
    (
    	id int (11) primary key auto_increment,
    	name char(255),sex char(255),
    	age int(11)
    )charset utf8;
    
    CREATE TABLE student_score
    (
    	id int (11) primary key auto_increment,
    	class char(255),score char(255),
    	student_id int(11)
    )charset utf8;

**（2）创建外键：**

ALTER TABLE student_score ADD CONSTRAINT s_id FOREIGN KEY(student_id)
    REFERENCES student(id);

**（3）查看表结构**

SHOW CREATE TABLE student;
    SHOW CREATE TABLE student_score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70][]

### **三、验证外键作用** ###

**1.先向主表中添加数据**

再向从表中添加数据（从表中的外键已在主表中存在），正常运行

#向student表中添加数据
    INSERT INTO 
    student(name,sex,age)
    VALUES('小明','男','20');
    
    #向student_score表中添加数据
    INSERT INTO student_score
    	(class,score,student_id) 
    VALUES
    	('语文','100',1),
    	('数学','99',1),
    	('英语','98',1);

数据插入正常，无报错、警告信息。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 1][]

**2.在事件触发限制使用默认值RESTRICT的情况下**

（1）向从表插入新行，外键值不在主表中，被阻止

INSERT INTO student_score(class,score,student_id) VALUES('语文','100',2);

![在这里插入图片描述][20200325151934842.png]

（2）从表修改外键值，新值不是主表的主键值，阻止修改

UPDATE student_score SET student_id = 2 WHERE student_id = 1;

![20200325152314288.png][]

（3）主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)

DELETE FROM student WHERE id = 1;

![20200325152546434.png][]

（4）主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)

UPDATE student SET id = 2 WHERE id = 1;

![20200325152826186.png][]

**3.更改事件触发限制为CASCADE**

#删除旧的外键
    ALTER TABLE student_score DROP FOREIGN KEY s_id;
    
    #添加新的外键，修改事件触发限制为CASCADE
    ALTER TABLE student_score ADD CONSTRAINT s_id FOREIGN KEY(student_id)
    REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE;

（1）查看表结构

SHOW CREATE TABLE student_score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 2][]

（2）查看此时两表中的数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 3][]

（3）此时，当主表修改主键值，从表中相关行的外键值将一起修改

UPDATE student SET id = 2 WHERE id = 1;

运行成功，查看两张表中的数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 4][]

（4）如果主表删除行，从表中的相关行将一起被删除

DELETE FROM student WHERE id = 2;

运行成功，查看两张表中的数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 5][]

4.所以，事件触发限制条件的不同，会造成两张表中的操作限制不同，其他几个限制条件相对好理解，大家可以自己进行尝试，体会其中的区别。  
事件触发限制条件的不同，会造成两张表中的操作限制不同，其他几个限制条件相对好理解，大家可以自己进行尝试，体会其中的区别。

### 四、删除外键约束 ###

1.语法

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
    
    ALTER TABLE student_score DROP FOREIGN KEY s_id;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70]: /images/20221005/65a0947c3e8045feb1ec697a62c1a2b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 1]: /images/20221005/da666f29487a4fc1abcddb074f31e065.png
[20200325151934842.png]: /images/20221005/97690b843d88439abe9217cd9bda9096.png
[20200325152314288.png]: /images/20221005/4db7a5ebfdd64ad58a48ea81424a13d1.png
[20200325152546434.png]: /images/20221005/41436d678d054c88972addb2d67019d3.png
[20200325152826186.png]: /images/20221005/4242d13d2b9f453789e6acdcb8c62873.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 2]: /images/20221005/d808a224e4b745b6afa2b1576c730362.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 3]: /images/20221005/915022fc0ca34d3e9d0c3e381a029ef4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 4]: /images/20221005/632bd7bf5be745d984d79391aa3ef110.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 5]: /images/20221005/9ec5e3d0a00d4043ba26817aecd72648.png