全方位揭秘！大数据从0到1的完美落地之Mysql表的完整性

我们已经知道了如何创建数据库、如何创建表、如何在表中进行数据的插入操作。但是在实际工作中，我们需要考虑插入到表中的数据是否是合乎逻辑的。因此，有时候我们需要在插入数据的时候，对数据进行校验，以保证插入表中的数据没有逻辑的错误。这就是数据的完整性。而为了保证数据的完整性，我们往往需要借助一些约束来实现。

数据的完整性约束可以分为三类: 实体完整性、域完整性和引用完整性。

实体完整性

我们将表中的一行数据，称为一个“实体”，而所谓的实体完整性约束，就是保证这一行数据的唯一的，不会出现重复的行数据。

为了达到这样的目的，实体完整性约束有三种: 唯一约束、主键约束、自增约束。

唯一约束

关键字: unique

作用: 顾名思义，就是修饰某一个字段，这个字段的值在一张表内不能重复！

• 单个键的唯一约束

  • 建表的同时添加

    -- 创建表
    create table `student` (
    	`id` int unique,		-- 修饰为unique，表示id字段的值不允许出现重复的情况
        `sname` varchar(50)
    );
    -- 或者
    create table `student` (
    	`id` int,
        `sname` varchar(50),
        unique(`id`)
    );
    
    -- 表创建完成后，在进行数据插入的时候，如果插入了重复的id，则第二次插入数据的时候会有错误提示，无法插入数据，保证这一行数据的唯一
    insert into student values (1, 'zhangsan');
    insert into student values (2, 'lisi');
    

  • 建表之后追加

    -- 创建表
    create table student (
    	id int,
        sname varchar(50)
    );
    
    -- 追加id为唯一约束
    alter table student add unique (`id`);
    

• 联合唯一约束

  • 描述

    所谓的“联合唯一约束”，指的是可以将多个字段绑定在一起进行非空的约束。只有当这些字段的值都相同的情况下，才会视为重复的数据，不允许继续插入。
    

  • 建表的时候添加

    create table `student` (
    	`id` int,
        `sname` varchar(50),
        unique(`id`, `sname`)
    );
    

  • 建表之后追加

    -- 创建表
    create table student (
    	id int,
        sname varchar(50)
    );
    
    -- 追加id为唯一约束
    alter table student add unique (`id`, `sname`);
    

  • 测试

    insert into `student` values (1, 'a');		-- 第一次插入数据，表中没有重复的行，可以添加
    insert into `student` values (1, 'b');		-- 虽然表中已经有id为1的行，但是sname并不重复，依然可以添加
    insert into `student` values (2, 'b');		-- 同理，表中虽然已经有sname为b的行，但是id并不重复，依然可以添加
    insert into `student` values (2, 'b');		-- id和sname的值都与已经存在的某一行数据重复，因此添加失败
    

• 移除约束

  alter table `student` drop index `sid`;
  

主键约束

关键字: primary key

其实，为了保证数据的唯一性，主键才是工作中最常用的约束方式。一般情况下，会将代表这一行数据的唯一标识的字段设置为主键。在进行查询操作的时候，通常情况下也是会使用主键进行行数据的区分。

主键的值不允许出现重复，也不允许出现NULL的情况。

添加主键有几种方式:

1. 在建表的时候添加主键，只添加一个主键

   create table student (
   	id int primary key,
       sname varchar(50)
   )
   

2. 在建表的时候添加主键，同时添加多个主键

   create table sc (
   	sid int,
       cid int,
       score int,
       primary key(sid, cid)	-- 如果有多个主键需要添加，不能在字段的后面直接写primary key。需要通过这样的方式来添加。
   )
   

3. 在建表完成后追加主键

   create table course (
   	cid int,
       cname varchar(50)
   )
   
   alter table course add primary key (cid);
   

删除表中的主键:

alter table course drop primary key;

自增约束

顾名思义，自增约束表示对某一个字段的值进行自动的增长。只能用在整型数据类型的主键约束上。表示在进行行数据的赋值的时候，如果不设置主键的值，这个值会自动的在之前的值的基础上+1。

-- 建表
create table student(
	sid int primary key auto_increment,
    sname varchar(50)
)

-- 插入数据
insert into student (sname) values ('zhangsan');		-- 没有设置主键sid的值，则此时的sid自动增长为1
insert into student values (10, 'lisi');				-- 此时已经设置了sid的值，则这行数据的sid就是10
insert into student (sname) values ('wangwu');			-- 没有设置主键sid的值，将会在目前最大的id基础上+1，也就是11

注意事项:

在表内会有一个变量，来记录当前的表最大的主键已经到达多少了。需要进行主键自增的时候，从这个记录了最大的主键的基础上进行自增，而不是简单的从上一行的基础上自增。

insert into student values (100, 'wangwu');
insert into student values (50, 'zhaoliu');
insert into student (sname) values ('tianqi');		-- 当前的表中，记录最大的主键值是100，因此这一行数据主键会自增到101

注意事项:

在删除表中的数据的时候，如果使用delete from进行删除，则无法删除表中记录的最大值。

insert into student values (200, 'xiaoming');
delete from student;
insert into student (sname) values ('xiaohei');		-- 此时的自增，结果依然是201

注意事项:

在删除表的时候，除了可以使用delete from进行删除，也可以使用truncate进行删除的操作。前面也说过这两种操作的区别，其中truncate是将现在的表进行drop，然后再按照之前的表结构(字段的名字、类型等信息)，创建出来一张跟这个表结构完全相同的表出来。那么问题来了，delete在删除数据的时候，只是删除数据，并不会对表结构进行修改，也就是说表中记录的最大的主键信息是会保留下来的。但是truncate是drop原来的表，然后创建一张新的表。那么之前的记录的最大主键值的信息也就不存在了。

insert into student values (200, 'xiaoming');
delete from student;
insert into student (sname) values ('xiaohei');		-- 自增的sid，值是201
truncate student;
insert into student (sname) values ('xiaolan');		-- 从头自增的sid，值是1

域完整性

域完整性约束，指的是对一个单元格内的数据进行约束，对某一个单元格内的数据进行约束。

非空约束

关键字: not null

顾名思义，就是对某一个字段进行修饰，修饰这一个字段的值不能是NULL。

create table student (
	sid int primary key,
    sname varchar(30) not null
)

-- 建表之后追加
alter table `student` change `sname` `sname` varchar(30) not null;
-- 建表之后移除
alter table `student` change `sname` `sname` varchar(30);

-- 正常的数据插入
insert into student values (10, 'Jerry');

-- 错误的写法，因为sname已经修饰为not null了
insert into student values (10, NULL);

-- 错误的写法，因为非空的字段的值没有设置
insert into student (sid) values (1);

默认值约束

关键字: default

修饰某一个字段，在这个字段进行赋值的时候没有赋值的情况下，这个字段将会拥有一个默认的初始值

create table student (
	sid int primary key,
    sname varchar(20) default 'unkonwn'
)

-- 正确的使用
insert into student (sid) values (100);		-- 没有设置sname的值，此时会采用默认的值 
insert into student values (101, default);	-- 直接使用关键字default，表示使用默认值

检查约束

检查约束，类似于Java中的枚举，约束这个字段的值只能够在有限的几个值内进行选择。

关键字: check

create table teacher( 
	tid int, 
	tname varchar(20), 
	tgender char(1) check(tgender in('f','m'))	-- 约束这个字段的值只能是m或者f
); 
insert into temp_5 values (1001,'zs','a'); 
select * from teacher;

关键字: enum

create table temp_6( 
 	tid int, 
 	tname varchar(20), 
 	tgender enum('f','m')		-- 枚举的写法
); 

引用完整性

主要就是外键约束(foreign key)

字段A的值，依赖于字段B的值。这两个字段可以在同一张表中，也可以在不同的表中。字段A所在的表称之为从表(副表)，字段B所在的表称之为主表(父表)。字段A的值也可以为null. 字段B必须为主键约束。

依赖的字段B，必须是目标表的主键

-- 1. 在建表的时候添加外键
create table tableName(
	tid int primary key auto_increment, 
	tname varchar(20), 
	tmgr int,
	constraint constraintName Foreign key(tmgr) references tableName(tid)
);

-- 2. 在建表之后追加
alter table tableName1 add constraint constraintName foreign key(colName1) references tableName2(colName2);

-- 3. 撤销外键
ALTER TABLE score DROP FOREIGN KEY score_ibfk_1;

EXT:

在添加主键的时候，可以同时设置删除和更新时的约束:

• RESTRICT: 默认的约束，不允许修改、删除被引用的字段的值
• CASCADE: 级联修改，在修改、删除被引用的字段的时候，引用方的一行数据也随之修改和删除
• SET NULL: 空值处理，在修改、删除被引用的字段的时候，引用方的引用字段的值会设置为NULL
• NO ACTION: 不处理，在修改、删除被引用的字段的时候，引用方的引用字段不作任何改变

语法:

alter table tableName1 add constraint constraintName foreign key(colName1) references tableName2(colName2) on delete restrict on update restrict;