4.数据的完整性

本章目标

• 数据完整性分类
• 完整性约束
• 更改表约束

本章内容

一、数据完整性分类

数据库中的数据是从外界输入的，而数据的输入由于种种原因，会发生输入无效或错误信息。保证输入的数据符合规定，成为了数据库系统，尤其是多用户的关系数据库系统首要关注的问题。数据完整性因此而提出。

数据完整性 = 精确性 + 可靠性

1、 实体完整性：

是指保证表中所有的行唯一。实体完整性要求表中的所有行都有一个唯一标识符。这个唯一标识符可能是一列，也可能是几列的组合，称为主键。也就是说，表中的主键在所有行上必须取唯一值。

2、 域完整性：

是指一个列的输入有效性，是否允许为空值。强制域完整性的方法有：限制类型、格式或可能值的范围。

3、 参照完整性：

是指保证主关键字和外部关键字之间的参照关系。它涉及两个或两个以上表数据的一致性维护。

外键值将引用表中包含此外键的记录和被引用表中主键与外键相匹配的记录关联起来。在输入、更改或删除记录时，参照完整性保持表之间已定义的关系，确保键值在所有表中一致。

二、 完整性约束

1、 MySQL 常见完整性约束：

• PRIMARY KEY 主码约束(主键)
• UNIQUE KEY 唯一性约束
• NOT NULL 非空值约束
• AUTO_INCREMENT 用于整数列默认自增1
• UNSIGNED 无符号整数
• DEFAULT default_value 默认值约束
• CHECK 约束
• FOREIGN KEY 外键约束
• DEFAULT CURRENT_TIMESTAMP 创建新记录时默认保存当前时间（仅适用timestamp数据列）
• ON UPDATE CURRENT_TIMESTAMP 修改记录时默认保存当前时间（仅适用timestamp数据列）
• CHARACTER SET name 指定字符集（仅适用字符串）

2、 空与非空

某数据单元的值为空表示该单元是以空值 (NULL) 作为其数据。NULL 不等于零或空白字符串：它表示没有输入任何内容，或提供了一个显式 NULL 值，通常表示该值未知或不适用 如果不允许该列的值为空，其定义的关键字为NOT NULL

例：如果不允许员工信息表中员工姓名列的值为空，则在创建表语句的列定义中加入NOT NULL 关键字即可：

CREATE TABLE EMPLOYEE
(
    id              int PRIMARY KEY,
    dept_id         CHAR(3),
    emp_name        VARCHAR(20) NOT NULL,
    ……
)

3、 主键

表中的一个列或多个列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键 主键可通过 PRIMARY KEY约束来创建

选择主键的要求

• 主键列值必须非空
• 一个表中只能有一个主键
• 单个列作为主键时，列的值必须唯一
• 多个列作为主键时，允许某个列的值存在重复，但作为主键的列的组合的值必须唯一
• 最少性，尽量选择单个键作为主键
• 稳定性，尽量选择数值更新少的列作为主键

创建主键

CREATE TABLE EMPLOYEE
(
    id          int PRIMARY KEY,
    dep_id          int,
    emp_name        VARCHAR(20) NOT NULL,
    gender      BIT NOT NULL,
)

复合主键

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `emp_name` varchar(20) DEFAULT NULL,
  `age` int(11) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `create_at` date DEFAULT NULL,
  PRIMARY KEY (id,emp_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

思考题：

1、在主键列输入的数值，允许为空吗? 2、一个表可以有多个主键吗? 3、在企业内部经常会出现同名同姓的员工，但是可以保证在某一个部门内不出现重名，那么可以组合部门和姓名两个字段一起来作为主键吗?

4、 标识列

AUTO_INCREMENT

4.1 、简介

自动标识列，在需要产生唯一标志符号或者顺序值时候，可用此属性。值一般从1开始，每行增加1，在插入NULL到一个AUTO_INCREMENT列时，MySQL会插入一个比该列中当前最大值大1 的值，一个表中最多能有一个有此属性的列。对于想使用此属性的列应该定义为NOT NULL,并定义为PRIMARY KEY 或者定义为UNIQUE键。

create table t(
    id int auto_increment  primary key
);

获取当前数据库表的自增字段数

select last_insert_id();

4.2 、插入自增

可以将id字段的值指定为0，MYSQL会自动帮你增一

INSERT INTO person(id,username,age,info) VALUES (0,'feicy',33,'student')

可以将id字段的值指定为NULL，MYSQL会自动帮你增一

INSERT INTO person(id,username,age,info) VALUES (NULL,'feicy',33,'student')

可以指定值

INSERT IGNORE INTO person(id,username,age,info) VALUES (16,'tom',88,'student')

也可以不写id的值，MYSQL会自动帮你增一

INSERT IGNORE INTO person(username,age,info) VALUES ('amy',12,'bb')

4.3 、插入自增思考题：

标识列允许为字符数据类型吗?

如果标识列A的初始值为1，增长量为1，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从多少开始?

5、 UNIQUE

唯一性，在插入数据时设置UNIQUE KEY的列的值不能出现重复，有以下特性：

• 一张表可以设置多列为UNIQUE

• 设置UNIQUE的列允许有多行该项为null

  CREATE TABLE employee (
   id int(11) NOT NULL AUTO_INCREMENT,
   emp_name varchar(20) ,
   UNIQUE KEY(emp_name),
   PRIMARY KEY (id)
  ) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8

6、 默认值

可以为列设定某一默认值，当记录在插入时未显式提供值的情况下，列的值会使用它的默认值 不可为标识列设定默认值 默认值可以是常量（数字，字符串），也可以是系统函数，也可以是NULL

image-20211225202633759

CREATE TABLE EMPLOYEE
(
    id        CHAR(8) PRIMARY KEY,
    dep_id        CHAR(3),
    emp_name      VARCHAR(20) NOT NULL,
    address   VARCHAR(100) DEFAULT '地址不详',
……
)

insert into employee1 VALUES('s1001',10,'tom',DEFAULT);

7、 CHECK

MySQL 8.0.16 版本以后， CHECK 约束功能对于所有的存储引擎都有效。

注：在 MySQL 8.0.16之前的版本中，虽然 CREATE TABLE 语句允许CHECK (expr)形式的检查约束语法，但实际上解析之后会忽略该子句.

语法：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

示例：

CREATE TABLE `employee` (
  `id` int NOT NULL ,
  `emp_name` varchar(20) ,
  `age` int NOT NULL check (emp_age>18),
  `address` varchar(50)
)

8、 外键

外键是用于建立和加强两个表数据之间关联性的约束。通过在数据表中保存相关实体记录的主键，可创建两个表之间的关联。 在记录中添加的相关实体的主键信息（唯一标识）就称为“外键”，例如在员工信息表中保存的部门编号、在订单信息表中保存的产品编号等等。

主键

create table student(
    id int not null auto_increment,
    stu_name varchar(20) not null,
    primary key(sid)
);

PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

外键（自动检查外键是否匹配，仅适用InnoDB）

create table score(
    id int not null auto_increment primary key,
    score int,
    student_id int,
    foreign key(student_id) references student(id)
);

foreign key(student_id) references student(id)，需要单独一行，不要直接放到 id int 后面

MyISAM引擎表不支持外键（Foreign Key）

主表和从表

1、当主表中没有对应的记录时，不能将记录添加到子表

成绩表中不能出现在学员信息表中不存在的学号；

2、不能更改主表中的值而导致子表中的记录孤立

把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变；

3、子表存在与主表对应的记录，不能从主表中删除该行

不能把有成绩的学员删除了

4、删除主表前，先删子表

先删学员成绩表、后删除学员信息表

9、扩展

开发时，创建时间、修改时间之类的我们一般可以直接使用系统当前时间即可，这时可以在定义表时直接指定

CREATE TABLE person
(
id int,
username varchar(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP on UPDATE CURRENT_TIMESTAMP
);
INSERT INTO person(id,username) VALUES (1001,'tom');
UPDATE person set username='merry' where id=1001;

三、 更改表约束

1、 添加主键约束：

alter table 表名 add constraint 主键 （形如：PK_表名） primary key 表名(主键字段);

ALTER TABLE person ADD constraint pk PRIMARY KEY(id);

2、 添加外键约束：

alter table 从表 add constraint 外键（形如：FK从表主表） foreign key 从表(外键字段) references 主表(主键字段);

alter table score add constraint fk foreign key(stu_id) references student(id);

3、 删除主键约束：

alter table 表名 drop primary key;

ALTER TABLE person DROP PRIMARY KEY;

4、 删除外键约束：

alter table 表名 drop foreign key 外键（区分大小写）;

alter table person drop foreign key fk;

5、 修改主键

alter table person drop primary key;--先删除主键
alter table person add primary key(id);--然后再添加主键

6、 删除被其他关联的父表：

1.先删除子表的外键约束，然后再删除父表；2.先删除子表，再删除父表；

7、 修改表的其他选项

alter table 表名 engine=新的存储引擎类型
alter table 表名 default charset=新的字符集
alter table 表名 auto_increment=新的初始值
alter table 表名 pack_keys=新的压缩类型

思维导图