MySQL系列5—数据库完整性

1. 数据库完整性

有时候虽然数据被存入数据库中，但是并不符合实际情况（人的岁数不可能超出范围等）。此时就需要数据库系统对数据的存入进行某种控制，就是下文讲到的完整性。

数据库的完整性是指数据的正确性和相容性。数据的正确性是指数据是否符合现实世界的语义、反映当前实际情况的；数据的相容性指数据库对同一对象在不同关系表的数据是符合逻辑的。

完整性分类

• 实体完整性：通过主键来标识某个独一无二的实体
• 参照完整性：通过外键标识某两个实体之间的联系
• 用户定义的完整性：针对某一具体字段的数据要求

2. 实体完整性

实体完整性指能够唯一标注某实体的属性/码（比如人的身份证号等），只有这样才能确保记录时符合实际的。

实体完整性在建表时用PRIMARY KEY定义。

# 方式一：列级约束条件
CREATE TABLE student_6
(
  `Sno` CHAR PRIMARY KEY,
  `Sname` VARCHAR(20) UNIQUE,
  `Ssex` CHAR(2), 
  `Sage` SMALLINT,
  `Sdept` CHAR(20)
) 
ENGINE = INNODB 
AUTO_INCREMENT = 0 
DEFAULT CHARSET=utf8 
COMMENT='学生表';

# 方式二：表级约束条件（可定义复合主键）
CREATE TABLE student_7
(
  `Sno` CHAR,
  `Sname` VARCHAR(20) UNIQUE,
  `Ssex` CHAR(2), 
  `Sage` SMALLINT,
  `Sdept` CHAR(20),
  PRIMARY KEY(Sno,Ssex)
) 
ENGINE = INNODB 
AUTO_INCREMENT = 0 
DEFAULT CHARSET=utf8 
COMMENT='学生表';

注意：

• 主键唯一，且不能为空
• 需要判断主键唯一，因此需要全表扫描，但这样太过耗时，因此关系系统一般在主键上自动建立一个索引

3. 参照完整性

参照完整性是模拟现实实际提出的一个理念，那就是相互依赖的关系。将此关系映射到关系型数据库的二维表中就是外码。

外键约束只能在建表时表级约束，或者修改表时提出

语法：

## 建表时添加外键约束
CREATE TABLE `student_3` (
 `Sno` CHAR PRIMARY KEY,
 `Ssex` CHAR DEFAULT NULL,
 `Sage` SMALLINT(6) DEFAULT 25,
 `teacher_id` INT(11) ,
 FOREIGN KEY(teacher_id) REFERENCES teacher(Sno)
)
ENGINE= INNODB 
DEFAULT CHARSET = utf8;


## 修改表时添加外键约束
ALTER TABLE student_2 ADD FOREIGN KEY(teacher_id) REFERENCES teacher(Sno);

在对被参照表或参照表进行更新时，可能破坏参照完整性，因此必须更新之前必须保证两个表的相容性。

4. 用户定义的完整性

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。

4.1 属性上的约束条件

在建表或修改表结构时，可以根据要求定义属性上的约束条件，如NOT NULL、UNIQUE、DEFAULT、CHECK()、auto_increment、zerofill、unsigned等。

遗憾的是，mysql不支持check约束，相应的功能可通过约束器实现。

# 用户定义的完整性
## 属性上的
CREATE TABLE `test_1`(
 `id` INT(11) PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(22) UNIQUE NOT NULL,
 `sex` VARCHAR(11) DEFAULT '男',
 `age` INT(11) CHECK(age>=10 AND age <= 30)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

# 更新属性时修改约束
ALTER TABLE test_1 CHANGE COLUMN `name` `namet` VARCHAR(22) UNIQUE NULL;
# 直接修改属性约束
ALTER TABLE test_1 MODIFY COLUMN `namet` VARCHAR(11) UNIQUE NOT NULL;

4.2 元组（行）上的约束条件

check既然在mysql中不起作用，那么还有其他约束条件能在元组（行）上起作用吗？

5. 完整性约束命名子句（CONSTRAINT）

以上讲解的完整性约束条件都是未命名定义，MySQL还提供了完整性约束命名子句CONSTRAINT，从而可以更灵活地增加、删除一个完整性约束条件。

# 表级命名constraint
CREATE TABLE `test_4`(
 `id` INT(11),
 `name` VARCHAR(22) UNIQUE NOT NULL,
 `sex` VARCHAR(11) DEFAULT '男',
 `age` INT(11) CHECK(age>=10 AND age <= 30),
 CONSTRAINT fk_1t PRIMARY KEY(id),	
 CONSTRAINT fk_2t UNIQUE(`name`)
)ENGINE=INNODB
DEFAULT CHARSET=utf8;

# 列级命名constraint
ALTER TABLE test_3 ADD CONSTRAINT fk_3t UNIQUE(age); # 有些约束无法用constraint命名

6. 断言（assertion）

MySQL提供断言来指定更一般性的约束，但断言范围更大，其能指定涉及多个表或聚集操作的完整性约束，任何对断言中所涉及关系的操作都会触发系统对断言的检查，断言不为真值的操作都会被拒绝。

遗憾的是，mysql不支持断言，取而代之是触发器。

常用语法：

# 创建
create assertion <断言名> <check 子句>

# 删除
drop assertion <断言名>

7. 触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程，其类似于约束，但比约束更灵活，可是实施更精细更复杂的检查和操作。

触发器定义在数据表上，在sql语句执行前/后触发，针对每一行执行，因此在对增删改查频繁的表上不建议使用触发器，除非确定触发器是非常高效的。

语法：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
        执行语句列表		# 切记导致死循环
END;

实例：

# 创建触发器
DELIMITER $$	# 需要重定义间隔符
CREATE TRIGGER insert_age AFTER INSERT
ON test.`student` FOR EACH ROW
BEGIN
  INSERT INTO test.`tt` (`name`, age) VALUES('cly',23);
END $$
DELIMITER ;

# 删除触发器
DROP TRIGGER [IF EXISTS] `insert_age`;

触发器能在一定程度上减少代码的编写，但其作为数据库特有存在，容易被忽略，这增加了系统复杂性，使排错更加费时，而存储过程需要显示调用。

其次并没有提升系统的性能，相反还可能降低数据库性能，在业务复杂的时候还可能造成死锁。