mysql表完整性约束 mysql中完整性约束有什么

 前言

数据完整性约束的概念：

在表中定义完整性约束是作为数据定义的一部分，定义了完整性约束，数据库会随时检测处于更新状态的数据库内容是否符合相关的完整性约束，保证数据的正确性与一致性。

完整性约束既能有效地防止对数据库的意外破坏和非法存取，提高完整性检测的效率，还能减轻数据库编程人员的工作负担。

常用的约束条件有6种：

重点：主键约束(primary key)、外键约束(foreign key)、唯一性约束(unique)，

           默认值约束(default)、非空约束(not null)、检查约束(check)。

数据完整性约束包括：

①：定义实体完整性（主键约束、候选键（即唯一性）约束）

②：定义参照完整性（外键约束）

③：用户定义完整性（非空约束、默认值、自增等）

Link Start!

目录

一、定义实体完整性（Entity Integrity）

1、主键约束(primary key)

2、唯一性（候选键）约束（unique）

 二、定义参照完整性（Referential Integrity）

1、外键约束（foreign key）

三、用户定义完整性（User-defined Integrity）

1、非空约束（not null）

2、默认值约束（default）

3、自增约束（auto_increment）

一、定义实体完整性（Entity Integrity）

定义实体完整性：

实体完整性规则是指关系的主属性不能取空值，即主键和候选键在关系中所对应的属性不能取空值，实体完整性通过主键约束和候选键约束来实现的。

1、主键约束(primary key)

主键遵循的规则如下：

1. 每一个表只能定义一个主键；
2. 主键的值（键值）能够唯一标识表中的每一行记录，且不能为null，即表中两条不同的记录在主键上不能具有相同的值。从中我们可以看出主键具有非空性与唯一性；
3. 组成复合主键的每一列都不能具有唯一性，但是每一列都具有非空性。
4. 一个列名在复合主键的列表中只能出现一次。

     5.定义主键约束后，数据库会自动为主键创建一个唯一索引，用于在查询中使用主键对数据进行快速检索，该索引名的默认为primary,也可以重新命名；

添加主键的方式：create table 和alter table

1、create table方式:列级完整性约束[基础]/标记完整性约束

①：列级完整性约束法：只需在表中某字段定义后加上关键字primary key即可 。

添加主键后的效果：

 

无法插入空数据，因为主键具有非空性。

无法插入相同的数据，因为主键具有唯一性，第二行代码重复插入了id "1"，若把1换成其他数字，则成功。

 

②：表级完整性约束：需要在表中所有字段定义后添加一条primary key语法格式的子句。

可以定义一个字段为主键：

也可以定义多个字段为主键，称为复合主键：

注意：定义复合主键只能用表级完整性定义！！

③：修改表的方式：

alter table 方式:alter table 表名 add [constraint 约束名] primary key(字段列表)

④：删除主键：

alter table 表名 drop primary key；

注意：该字段删除主键之后，仍然具有非空性，但不具有唯一性。

2、唯一性（候选键）约束（unique）

如果希望表中的某个字段值不重复，是唯一的值，可以为该字段添加唯一性约束。

一张表中可以有多个唯一性约束，且满足唯一性约束的字段可以取null值，且由于唯一性的性质，只能取一个空值。

主键约束与候选键约束的区别如下：

1. 一张表只能定义一个主键，但可以定义多个候选键；
2. 定义主键的列不能为null，定义为候选键允许null存在；
3. 定义主键约束时，系统会自动产生主键索引，定义候选键时，系统自动产生候选键索引。

创建候选键约束用法：create table语句中使用关键字unique来实现。    

①：列级完整性约束方式定义候选键，及在某个字段后加关键字unique。

②：表级完整性约束方式定义候选键，并指定候选键约束名称,适合定义多列所构成的候选键。

其定义语法如下：

[constraint 约束名字 ] unique(列名1,..,列名n)

其中，MUL的意思为多个，表示该表有多个候选键。

那么在实际操作中要怎么知道到底哪些是候选键呢？

可以使用show 指令来进行查看：

show create table  表名 \G

③：修改表的方式：

alter table 表名 add [constraint 约束名字] unique(列名1,..,列名n);

  ④：删除候选键约束用法：

Alter table语句中使用关键字UNIQUE来实现。    

删除候选键约束时，实际删除的是唯一性索引，应使用DROP INDEX子句删除.

如果没有给约束命名，自动将字段名定义为索引名

其用法如下：

alter tbale 表名 drop index 候选键约束名|候选键字段名;

二、定义参照完整性（Referential Integrity）

定义参照完整性的概念：

现实世界中的实体之间往往存在着某种联系，在关系模型中实体及实体之间的联系都是用关系来描述的，因此可能存在着关系与关系间的引用。

例如：学生实体和专业实体可以用下面的关系模式表示，其中主键用下划线标识：
学生（学号，姓名，性别，年龄，班级编号）
班级（班级编号，班级名称，年级，班级最大人数）

这两个关系之间存在着属性的引用，即学生关系引用了班级关系的主键“班级编号”。

显然，学生关系中的“班级编号”值必须在班级关系中确实存在的班级编号，即班级关系中有该班的记录。

这也就是说，学生关系中的“班级编号”的取值需要参照班级关系中“班级编号”的取值，“班级编号”是班级关系的主键，在学生关系中“班级编号”是外键。

定义参照完整性约束定义外键与主键之间的引用规则，即外键的取值或为空，或者等于被参照关系中的某个主键的值，需要遵守以下规则：

被参照表必须定义主键或候选键；

条件①：一定要先把被参照表定义好。

条件②：参照表外键对应的列的数目要和被参照表主键或者候选键对应的列的数目一致。

条件③：参照表外键对应的列的数据类型要和被参照表主键或者候选键对应的数据类型一致。

条件④：被参照表的主键不能为空，但是参照表外键允许为空。

条件⑤：只有存储引擎innodb支持外键，其他的不支持。

1、外键约束（foreign key）

（——用于多表之间的操作——）

创建表的同时，创建外键约束，参照完整性约束方式定义外键，语法：

[constraint 外键约束名字] foreign key (外键对应列) references 被参照表(被参照表对应列);

例1：

先创建被参照表tb_class,该表包括班级编号，班级名称，所属院系，年级，班级最大人数，建表如下：

use db_school;

create table tb_class(
       classno char(6)  primary key, 
       classname varchar(20) , 
       department varchar(30) ,
       grade int,
       classnum int
       constraint uq_class unique(classname)
);

再创建参照表tb_student，要求定义classno为外键。

create table tb_student(
       studentno char(10),
       studentname varchar(20),
       sex char(2),
       birthday date,
       classno char(6) ，
       primary key(studentno),
       constraint fk_classno foreign key(classno) references tb_class(classno)
);

上面的语句中，constraint fk_student foreign key (classno)references tb_class(classno) 完成外键约束命名和定义；

也可以写成：foreign key(classno) references tb_class(classno), 这种写法没有给出约束名，系统会自动生成一个约束名。

添加表约束，用alter table...add constraint语句为参照表添加外键约束，语法如下：

alter table  参照表  add  constraint  外键约束名  foreign key(参照表外键字段1,...字段名n) references 被参照表表名(被参照表字段名1,...字段名n);

例：为上面的tb_student中的 classname添加外键,约束名为fk_classname，sql语句如下：

alter table tb_student add constraint fk_classname foreign key(classname) references tb_class(classname);

修改表的方式：

alter table 表名 add [constrant 外键约束名] foregin key(外键对应列) reference 被参照表表名(主键或者候选键对应的列)

删除表外键约束：

alter table 参照表 drop foreign key 外键约束名;

三、用户定义完整性（User-defined Integrity）

概念：

针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

主要包括非空约束、唯一约束、检查约束、主键约束、外键约束

1、非空约束（not null）

非空约束是指字段的值不能为null，null值就是没有值或值空缺， 不是空串（‘ ’）；

对于使用了非空约束的字段，如果 用户在添加数据时没有给其指定值，数据库系统会报错。

在mysql中，非空约束的定义可以使用 create table 或 alter table语句，

在某个列定义后面加上关键字 not null作为限定词，来约束该列的取值不能为空。

1、创建表时，为字段添加非空约束

mysql> create table d(
    -> id int not null,
    -> name varchar(30) not null,
    -> age int not null
    -> );
Query OK, 0 rows affected (0.01 sec)

2、修改表，给已经存在的字段添加非空约束

alter table 表名 modify 字段名 数据类型 not null;

mysql> alter table b modify age int not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意：如果对一个候选键添加非空约束，则该键变为主键。但之后的候选键进行相同操作时不能变换，因为主键具有唯一性。

例如以下 的id字段，其原本为候选键约束，但为其添加非空约束后变为了主键。

3、修改表，添加新的字段并添加非空约束条件

alter table 表名 add 新字段名 新数据类型 not null;

mysql> alter table b add address varchar(60) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

4、删除表中字段的非空约束

alter table 表名 modify 字段名 数据类型 null;

mysql> alter table b modify age int null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、默认值约束（default）

默认值约束用指定一个字段的默认值。

如果没有在该字段值填写数据，则该字段将自动填入这个默认值。

1、创建表时，给字段增加默认值约束，

mysql> create table e(
    -> id int default  1,
    -> name varchar(30) default '张三',
    -> birth date default '2000-01-01'
    -> );
Query OK, 0 rows affected (0.01 sec)

 以上创建的e表中，id、name、birth三个字段给了默认值，

在插入数据的时候如果不输入其他值，给一个default,mysql会自动给默认值。

2、修改表，对表中已有的字段设置默认值

alter table 表名 alter 字段名 set default  默认值;

mysql> alter table b alter address set default '江软大';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、修改表，在表中新增字段且为该字段设置默认值

alter table 表名 add 字段名 数据类型 default  默认值

新增一个birth字段，为其设置默认值，并设置非空约束。

mysql> alter table b add birth date not null default '2020-4-1';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

4、删除表中某个字段的默认值

alter table 表名 alter 字段名 drop default;

mysql> alter table b alter address drop default;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、自增约束（auto_increment）

在MySQL中，可通过关键字auto_increment为列设置自增属性，

只有——整型列——才能设置此属性，因为我们不可能对某个字母或者汉字进行自增，该约束针对的是数量。

每个表只能定义一个auto_increment列，而且前提是，必须先在该列上定义主键约束（primary key）或候选键（unique）。

 

1：列级完整性约束方法，在创表时给字段增加自增约束：

creat table 表名(字段 数据类型 primary key|unique auto_increment)；

2：修改表的方法：

①：为表中已有的（已经定义为主键或候选键）字段增加自增约束：

 

alter table 表名  modify 字段名 数据类型 auto_increment；

②：为表中新增字段并同时增加自增约束：

alter table 表名 add字段名 数据类型 primary key auto_increment ；

删除表的自增主键约束，分两步：

第一步，

修改该字段的数据类型方法，且新数据类型不能为int型，去除自增约束属性，方法如下：

alter table 表名 modify字段名 新数据类型;

第二步，

删除该字段的主键或者候选键，方法如下：

alter table 表名 drop primary key;

alter table 表名 drop index 约束名|约束字段名;

第三步，

将修改后的数据类型改为原本的int型，方法如下：

注：前两步不能颠倒顺序