MySQL总结(七)数据库表的约束-详解

偏执的太偏执、 2023-02-24 11:17 35阅读 0赞

[精选30+云产品，助力企业轻松上云！>>> ![hot3.png][]][30_ _hot3.png]

![up-a344f41e81d3568e3310b5da00c57ced8ea.png][]

# 数据库表的约束 #

## 1. 数据库约束的概述 ##

### 1.1 约束的作用： ###

> 对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

### 1.2 约束种类： ###

<table> 
 <thead> 
  <tr> 
   <th>约束名</th> 
   <th>约束关键字</th> 
   <th>特点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键</td> 
   <td>primary key</td> 
   <td>保证以该列字段查询，查出唯一数据</td> 
  </tr> 
  <tr> 
   <td>唯一</td> 
   <td>unique</td> 
   <td>保证该列数据的唯一性</td> 
  </tr> 
  <tr> 
   <td>非空</td> 
   <td>not null</td> 
   <td>保证该列数据不能为null</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>foreign key</td> 
   <td>设计多表，从表的数据，依赖于主表；</td> 
  </tr> 
  <tr> 
   <td>默认</td> 
   <td>default</td> 
   <td>如果该列数据不存数据，那么有一个默认值； timestamp如果添加数据时，为null，那么默认当前系统时间</td> 
  </tr> 
  <tr> 
   <td>检查约束</td> 
   <td>check 注意:mysql不支持,后期在oracle总结中进行讲解</td> 
   <td>插入或修改数据时,sql检查是否符合条件</td> 
  </tr> 
 </tbody> 
</table>

## 2. 主键约束 ##

### 2.1 主键的作用 ###

> 用来唯一标识数据库中的每一条记录

![up-ad7c92265d586540e0c4618db9e0475fcb3.png][]![up-d8aa630458010c0063b3af8f0cb989e6b3d.png][]

### 2.2 哪个字段应该作为表的主键？ ###

> 通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。
> 
> **主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。**
> 
> 如: 身份证,学号不建议做成主键

### 2.3 创建主键 ###

*  关键字：primary key
 *  特点：
    
     *  非空 not null
     *  唯一 unique
 *  创建方式

/* 
    在创建表的时候给字段添加主键
    -- 格式: 字段名 字段类型 primary key
    */
    /*
    在已有表中添加主键
    -- alter table 表名 add primary key(字段名);
    */

-- 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键
    create table st5 (
      id int primary key, -- id 为主键
      name varchar(20),
      age int
    );
    desc st5;

![up-ffea9c824b236337a5f816e9a5a18c27110.png][]

-- 插入重复的主键值
    insert into st5 values (1, '关羽', 30);
    -- 错误代码： 1062 Duplicate entry '1' for key 'PRIMARY'
    insert into st5 values (1, '关云长', 20);
    
    select * from st5;
    
    -- 插入 NULL 的主键值, Column 'id' cannot be null
    insert into st5 values (null, '关云长', 20);

### 2.4 删除主键 ###

-- 删除 st5 表的主键
    alter table st5 drop primary key;
    
    -- 添加主键
    alter table st5 add primary key(id);

### 2.5 主键自增 ###

> 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
> 
> auto\_increment 表示自动增长(字段类型必须是整数类型)

-- 插入数据
    insert into st6 (name,age) values ('小乔',18);
    insert into st6 (name,age) values ('大乔',20);
    -- 另一种写法
    insert into st6 values(null,'周瑜',35);
    select * from st6;

![up-81073bcb41cd27a5870f7d21eeeac78667b.png][]

### 2.6 修改自增长的默认值起始值 ###

> 默认地 AUTO\_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下列 SQL 语法

*  创建表时指定起始值

-- 格式:
    create table 表名(
      列名 int primary key auto_increment
    ) auto_increment = 起始值;

-- 指定起始值为 1000
    create table st4 (
      id int primary key auto_increment,
      name varchar(20)
    ) auto_increment = 1000;
    
    insert into st4 values (null, '孔明');
    select * from st4;

*  创建好表后修改起始值

-- 格式:
    alter table 表名 auto_increment = 起始值;

alter table st4 auto_increment = 2000;
    insert into st4 values (null, '刘备');

![up-086deceb1e07146c384a320c63e418c0efc.png][]

### 2.7 delete 和truncate 对自增长的影响 ###

*  delete：删除所有的记录之后，对自增长没有影响。

![up-6ef7ca2d59a9a1eec2a7f06e2c6cf3f4e2a.png][]

*  truncate：删除以后，自增长又重新开始。

![up-efac8e6efbe101acf061f790702f8209082.png][]

## 3.唯一约束 ##

> 什么是唯一约束：表中某一列不能出现重复的值

### 3.1 唯一约束的基本格式 ###

字段名 字段类型 unique

### 3.2 实现唯一约束 ###

-- 创建学生表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生
    create table st7(
      id int,
      name varchar(20) unique
    );
    
    -- 添加一个学生
    insert into st7 values(1,'张三');
    
    select * from st7;
    -- Duplicate entry '张三' for key 'name'
    insert into st7 values (2, '张三');

*  插入多个 null 会怎样？

insert into st7 values (2, null);
    insert into st7 values (3, null);
    select * from st7;

![up-a4d2f40fb22e42f99299059478c1aa5c609.png][]

## 4.非空约束 ##

> 什么是非空约束：某一列不能为null。

### 4.1 非空约束的基础语法格式 ###

字段名 字段类型 not null

-- 创建表学生表 st8, 包含字段(id,name,gender)其中 name 不能为 NULL
    create table st8 (
      id int,
      name varchar(20) not null,
      gender char(1)
    );
    
    -- 添加一条记录其中姓名不赋值
    insert into st8 values (1,'张三疯','男');
    select * from st8;
    
    -- Column 'name' cannot be null
    insert into st8 values (2,null,'男');

## 5.默认值 ##

> 什么是默认值：某列没有添加数据时，使用默认数据

字段名 字段类型 default 默认值

-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
    create table st9 (
      id int,
      name varchar(20),
      address varchar(20) default '广州'
    );
    
    -- 添加一条记录,使用默认地址
    insert into st9 values (1, '李四', default);
    select * from st9;
    
    insert into st9 (id,name) values (2, '李白');
    
    -- 添加一条记录,不使用默认地址
    insert into st9 values (3, '李四光', '深圳');

*  如果一个字段设置了非空与唯一约束，该字段与主键的区别？
    
     *  1）**主键数在一个表中，只能有一个**。不能出现多个主键。主键可以单列，也可以是多列。
     *  2）自增长只能用在主键上。

## 6.外键约束 ##

### 6.1 单表的缺点 ###

*  创建一个员工表包含如下列(id, name, age, dep\_name, dep\_location),id 主键并自动增长,添加 5 条数据

CREATE TABLE emp (
      id INT PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(30),
      age INT,
      dep_name VARCHAR(30),
      dep_location VARCHAR(30)
    );
    -- 添加数据
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
    INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

*  以上数据表的缺点:
    
     *  1.  数据冗余
     *  1.  后期还会出现增删改的问题

![up-7e5711c033772e5fe4e5d0f0f14c4de59ce.png][]

### 6.2 解决方案 ###

![up-700491b1461b9d99ca8a3a6905894c1c3d1.png][]

-- 解决方案：分成 2 张表
    -- 创建部门表(id,dep_name,dep_location)
    -- 一方，主表
    create table department(
      id int primary key auto_increment,
      dep_name varchar(20),
      dep_location varchar(20)
    );
    -- 创建员工表(id,name,age,dep_id)
    -- 多方，从表
    create table employee(
      id int primary key auto_increment,
      name varchar(20),
      age int,
      dep_id int -- 外键对应主表的主键
    );
    -- 添加 2 个部门
    insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
    select * from department;
    -- 添加员工,dep_id 表示员工所在的部门
    INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
    select * from employee;

*  问题:

> 当我们在 employee 的 dep\_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，实际应用中不能出现这种情况。employee 的 dep\_id 中的数据只能是 department 表中存在的 id ![up-5db7c840ac845f5059e240f4c31bece2125.png][]

*  目标: 需要约束 dep\_id 只能是 department 表中已经存在 id
 *  解决方案: 使用外键约束

### 6.3 什么是外键约束 ###

*  什么是外键：在从表中与主表对应的那一列。如：员工表中的dep\_id
 *  主表：一方，用来约束别人的表
 *  从表：多方，被别人约束的表

![up-b2f7cb0fec584d0be99740ea7b06708139d.png][]

### 6.4 创建约束的语法 ###

*  新建表时增加外键：

[constraint] [外键约束名称] foreign key (外键字段名) references 主表名(主键字段名)

*  已有表增加外键：

alter table 从表 add [constraint] [外键约束名称] foreign key (外键字段名) references 主表(主键字段名)

*  具体操作：

-- 1) 删除副表/从表 employee
    drop table employee;
    -- 2) 创建从表 employee 并添加外键约束 emp_depid_fk
    -- 多方，从表
    create table employee(
      id int primary key auto_increment,
      name varchar(20),
      age int,
      dep_id int, -- 外键对应主表的主键
      -- 创建外键约束
      constraint emp_depid_fk foreign key (dep_id) references department(id)
    );
    -- 3) 正常添加数据
    INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
    select * from employee;
    -- 4) 部门错误的数据添加失败
    -- 插入不存在的部门
    -- Cannot add or update a child row: a foreign key constraint fails
    INSERT INTO employee (NAME, age, dep_id) VALUES ('老张', 18, 6);

![up-a3a908c0c5466800f5dd364cd3a4d3284a1.png][]

### 6.5 删除外键 ###

alter table 从表 drop foreign key 外键名称;

-- 删除 employee 表的 emp_depid_fk 外键
    alter table employee drop foreign key emp_depid_fk;
    
    -- 在 employee 表存在的情况下添加外键
    --      从表employee          约束名称emp_depid_fk       从表字段dep_id        主表department 主表字段id
    alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);

### 6.6 外键的级联 ###

*  出现新问题：

select * from employee;
    select * from department;
    -- 要把部门表中的 id 值 2，改成 5，能不能直接更新呢？
    -- Cannot delete or update a parent row: a foreign key constraint fails
    update department set id=5 where id=2;
    -- 要删除部门 id 等于 1 的部门, 能不能直接删除呢？
    -- Cannot delete or update a parent row: a foreign key constraint fails
    delete from department where id=1;

*  什么是级联操作：

> 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

<table> 
 <thead> 
  <tr> 
   <th>级联操作语法</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ON UPDATE CASCADE</td> 
   <td>级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新</td> 
  </tr> 
  <tr> 
   <td>ON DELETE CASCADE</td> 
   <td>级联删除</td> 
  </tr> 
 </tbody> 
</table>

-- 删除 employee 表，重新创建 employee 表，添加级联更新和级联删除
    drop table employee;
    create table employee(
      id int primary key auto_increment,
      name varchar(20),
      age int,
      dep_id int, -- 外键对应主表的主键
      -- 创建外键约束
      constraint emp_depid_fk foreign key (dep_id) references
      department(id) on update cascade on delete cascade
    )
    -- 再次添加数据到员工表和部门表
    INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
    -- 删除部门表？能不能直接删除？
    drop table department;
    -- 把部门表中 id 等于 1 的部门改成 id 等于 10
    update department set id=10 where id=1;
    select * from employee;
    select * from department;
    -- 删除部门号是 2 的部门
    delete from department where id=2;

## 7. 数据约束小结 ##

<table> 
 <thead> 
  <tr> 
   <th>约束名</th> 
   <th>约束关键字</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键</td> 
   <td>primary key</td> 
   <td>唯一，非空</td> 
  </tr> 
  <tr> 
   <td>唯一</td> 
   <td>unique</td> 
   <td>这一列不能有重复值</td> 
  </tr> 
  <tr> 
   <td>非空</td> 
   <td>not null</td> 
   <td>这一列必须有值</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>foreign key</td> 
   <td>主表中的主键列，在从表中的外键列</td> 
  </tr> 
  <tr> 
   <td>默认</td> 
   <td>default</td> 
   <td>如果一列没有值，使用默认值</td> 
  </tr> 
  <tr> 
   <td>检查约束</td> 
   <td>check 注意:mysql不支持,后期在oracle总结中进行讲解</td> 
   <td>插入或修改数据时,sql检查是否符合条件</td> 
  </tr> 
 </tbody> 
</table>

## OK,这篇就到这里 ##

[hot3.png]: /images/20230209/466de683410b41d7939024fa1221a672.png
[30_ _hot3.png]: https://www.oschina.net/action/visit/ad?id=1221
[up-a344f41e81d3568e3310b5da00c57ced8ea.png]: /images/20230209/048356e416664648b07962549aaa9ab1.png
[up-ad7c92265d586540e0c4618db9e0475fcb3.png]: https://oscimg.oschina.net/oscnet/up-ad7c92265d586540e0c4618db9e0475fcb3.png
[up-d8aa630458010c0063b3af8f0cb989e6b3d.png]: /images/20230209/f6476e77b9b04c15b69e82cbd8de139e.png
[up-ffea9c824b236337a5f816e9a5a18c27110.png]: https://oscimg.oschina.net/oscnet/up-ffea9c824b236337a5f816e9a5a18c27110.png
[up-81073bcb41cd27a5870f7d21eeeac78667b.png]: https://oscimg.oschina.net/oscnet/up-81073bcb41cd27a5870f7d21eeeac78667b.png
[up-086deceb1e07146c384a320c63e418c0efc.png]: https://oscimg.oschina.net/oscnet/up-086deceb1e07146c384a320c63e418c0efc.png
[up-6ef7ca2d59a9a1eec2a7f06e2c6cf3f4e2a.png]: https://oscimg.oschina.net/oscnet/up-6ef7ca2d59a9a1eec2a7f06e2c6cf3f4e2a.png
[up-efac8e6efbe101acf061f790702f8209082.png]: https://oscimg.oschina.net/oscnet/up-efac8e6efbe101acf061f790702f8209082.png
[up-a4d2f40fb22e42f99299059478c1aa5c609.png]: https://oscimg.oschina.net/oscnet/up-a4d2f40fb22e42f99299059478c1aa5c609.png
[up-7e5711c033772e5fe4e5d0f0f14c4de59ce.png]: https://oscimg.oschina.net/oscnet/up-7e5711c033772e5fe4e5d0f0f14c4de59ce.png
[up-700491b1461b9d99ca8a3a6905894c1c3d1.png]: https://oscimg.oschina.net/oscnet/up-700491b1461b9d99ca8a3a6905894c1c3d1.png
[up-5db7c840ac845f5059e240f4c31bece2125.png]: https://oscimg.oschina.net/oscnet/up-5db7c840ac845f5059e240f4c31bece2125.png
[up-b2f7cb0fec584d0be99740ea7b06708139d.png]: https://oscimg.oschina.net/oscnet/up-b2f7cb0fec584d0be99740ea7b06708139d.png
[up-a3a908c0c5466800f5dd364cd3a4d3284a1.png]: https://oscimg.oschina.net/oscnet/up-a3a908c0c5466800f5dd364cd3a4d3284a1.png