MySQL之约束

雨点打透心脏的1/2处 2024-03-30 13:31 34阅读 0赞

## 1. 约束 ##

### 1.1 为什么需要约束 ###

**就是为了保证数据的完整性！**

**数据完整性**（Data Integrity）是指数据的**精准性**（Accuracy）和**可靠性**（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

**实体完整性**：例如，同一个表中，不能存在两条完全相同无法区分的记录。  
**域完整性**：例如：年龄范围0-120，性别范围“男/女”。  
**引用完整性**：例如：员工所在部门，在部门表中要能找到这个部门。  
**用户自定义完整性**：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

### 1.2 什么叫约束 ###

约束：对表中字段的限制

### 1.3 约束的分类 ###

角度1：约束的字段的个数  
单列约束 vs 多列约束

角度2：约束的作用范围  
列级约束：将此约束声明在对应字段的后面。  
表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束。

角度3：约束的作用（或功能）  
not null (非空约束)  
unique (唯一性约束)  
primary key (主键约束)  
foreign key (外键约束)  
check (检查约束)  
default (默认值约束)

### 1.4 如何添加/约束？ ###

CREATE TABLE时添加约束  
ALTER TABLE时增加约束、删除约束

# 方式1：
    ALTER TABLE test2
    ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
    
    # 方式2：
    ALTER TABLE test2
    MODIFY last_name VARCHAR(15) UNIQUE;
    
    # 复合唯一约束：
    CREATE TABLE student_course(
    	id INT,
    	sid INT, #学号
    	cid INT, #课程编号
    	score INT,
    	UNIQUE KEY(sid，cid) #复合唯一
    );

### 1.5 删除唯一性约束 ###

1.  添加唯一性约束的列上也会自动创建唯一索引。
2.  删除唯一约束只能通过别除唯一索引的方式删除。
3.  删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
4.  如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同：如果是组合列，那么默认就和（）中排在第一个的列名相同。也可以自定义唯一性约束名。

# 如何删除唯一性索引
    ALTER TABLE test2
    DROP INDEX last_name;

## 2. 主键约束 ##

MySQL的主键名总是叫PRIMARY

删除主键约束(在实际开发过程中是不会去做的)

ALTER TABLE test6
    DROP PRIMARY KEY;

## 3. 自增长列：AUTO\_INCREMENT ##

1.  自增长列约束的列必须是键列（主键列，唯一键列）
2.  自增约束的列的数据类型必须是整数类型

ALTER TABLE books
    MODIFY id INT AUTO_INCREMENT;

## 4. 外键约束 ##

### 4.1 作用 ###

作用：限定某个表的某个字段的引用完整性

### 4.2 要求 ###

1.  在创建外键约束时，如果不给外键约束命名，**默认名不是列名，而是自动产生一个外键名**，也可以指定外键约束名。
2.  \*\*当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。\*\*但是索引名是列名，不是外键的约束名。（根据外键查询效率很高）
3.  删除外键约束后，必须**手动**删除对应的索引。

### 4.3 约束等级 ###

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录。

Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但要注意子表的外键列不能为not null。

No action方式：如果子表中由匹配的记录，则不允许对父表对应候选键进行update/delete操作。

Restrict方式：同no action，都是立即检查外键约束。

对于外键约束，最好时采用：ON UPDATE CASCADE ON DELETE RESTRICT

### 4.4 实际开发规范 ###

不使用外键与级联，一切外键概念必须在应用层解决。  
外键与级联更新适用于**单机低并发**，不适合**分布式**、**高并发集群**；级联更新是强阻塞，存在数据库**更新风暴**的风险；外键影响数据库的**插入速度**。

## 5. 面试相关 ##

1.  **为什么建表时，加not null default"或default 0**  
    答：不想让表中出现nu值。
2.  **为什么不想要null的值**  
    答：(1)不好比较。null是一种特殊值，比较时只能用专门的is null和is not null来比较。碰到运算符，通常返回  
    null。  
    (2)效率不高，影响提高索引效果。因此。我们往往在建表时not null default"或default 0。
3.  **带AUTO\_INCREMENT约束的字段值是从1开始的吗？**  
    答：在MySQL中，默认AUTO\_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性  
    (AUTO\_INCREMENT)的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增  
    加。添加主键约束时，往往需要设置字段自动增加属性。
4.  **并不是每个表都可以任意选择存储引擎？**  
    外键约束(FOREIGN KEY)不能跨引擎使用。  
    MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

参考资料：[MySQL数据库教程天花板][MySQL]

[MySQL]: https://www.bilibili.com/video/BV1iq4y1u7vj?p=73&vd_source=a1af903e57688df870625021f7f3a7eb