约束

数据完整性(Data Integrity)

1、指数据的精确性(Accuracy)和可靠性(Reliability)

2、防止数据库中存在不符合语义规定的数据,防止因错误信息的输入输出,造成无效操作或错误信息

3、为了保证数据的完整性,SQL 规范以约束的方式对表数据进行额外的条件限制

(1)实体完整性(Entity Integrity) :例如,同一个表中,不能存在两条完全相同无法区分的记录

(2)域完整性(Domain Integrity) :例如:年龄范围 0-120,性别范围 男/女

(3)引用完整性(Referential Integrity) :例如:员工所在部门,在部门表中要能找到这个部门

(4)用户自定义完整性(User-defined Integrity) :例如:用户名唯一、密码不能为空等

 

约束

1、表级的强制规定,确保数据库的数据满足特定规则

2、规定约束

(1)在创建表时,通过 CREATE TABLE 语句

(2)在表创建之后,通过 ALTER TABLE 语句

 

约束分类

1、根据约束数据列的限制

(1)单列约束:每个约束只约束一列

(2)多列约束:每个约束可约束多列数据

2、根据约束的作用范围

(1)列级约束:只能作用在一个列上,跟在列的定义后面

(2)表级约束:可以作用在多个列上,不与列一起,而是单独定义

  位置 支持的约束类型 是否可以起约束名
列级约束 列的后面 语法都支持,但外键没有效果 不可以
表级约束 所有列的下面 默认和非空不支持,其他支持  可以(主键没有效果)

3、根据约束起的作用

(1)NOT NULL:非空约束,规定某个字段不能为空

(2)UNIQUE:唯一约束,规定某个字段在整个表中是唯一的

(3)PRIMARY KEY:主键(非空且唯一)约束

(4)FOREIGN KEY:外键约束

(5)CHECK:检查约束,MySQL 不支持 CHECK 约束,但可以使用,而没有任何效果 

(6)DEFAULT:默认值约束

 

非空约束

1、关键字:NOT NULL

2、特点

(1)默认,所有的类型的值都可以是 NULL,包括 INT、FLOAT 等数据类型

(2)非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空

(3)一个表可以有很多列都分别限定非空

(4)空字符串 '' 不等于 NULL,0 也不等于 NULL

3、添加非空约束

(1)建表时

CREATE TABLE 表名称(
    字段名 数据类型,
    字段名 数据类型 NOT NULL,
    字段名 数据类型 NOT NULL
);

(2)建表后

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

4、删除非空约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
#或
ALTER TABLE 表名称 MODIFY 字段名 数据类型;

 

唯一性约束

1、作用:用来限制某个字段/某列的值不能重复

2、关键字:UNIQUE

3、特点

(1)同一个表可以有多个唯一约束

(2)唯一约束可以是某一个列的值唯一,也可以多个列组合的值唯一

(3)唯一性约束允许列值为空

(4)在创建唯一约束的时候,如果不给唯一约束命名,就默认和列名相同

(5)MySQL会给唯一约束的列上默认创建一个唯一索引

4、添加唯一约束

(1)建表时

CREATE TABLE 表名称(
    字段名 数据类型,
    字段名 数据类型 UNIQUE,
    字段名 数据类型 UNIQUE KEY,
    字段名 数据类型
);
CREATE TABLE 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    [CONSTRAINT 约束名] UNIQUE KEY(字段名)
);

(2)建表后指定唯一键约束

#字段列表中如果是一个字段,表示该列的值唯一
#字段列表中如果是多个字段,多个字段名用逗号分隔,那么复合唯一,即多个字段的组合是唯一的
ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表);
ALTER TABLE 表名称 MODIFY 字段名 字段类型 UNIQUE;

5、删除唯一约束

(1)添加唯一性约束的列上也会自动创建唯一索引

(2)删除唯一约束只能通过删除唯一索引的方式删除

(3)删除时需要指定唯一索引名,唯一索引名就和唯一约束名相同

(4)如果创建唯一约束时,未指定名称,如果是单列,就默认和列名相同;如果是组合列,那么默认和 () 中排在第一个的列名相同,也可以自定义唯一性约束名

ALTER TABLE USER DROP INDEX 唯一约束名;

 

主键约束

1、作用:唯一标识表中的一行记录

2、关键字:PRIMARY KEY

3、特点

(1)主键约束 <=> 唯一约束 + 非空约束,主键约束列不允许重复,也不允许出现空值

(2)一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建

(3)主键约束对应着表中的一列或者多列(复合主键)

(4)如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复

(5)MySQL 主键名总是 PRIMARY,自定义主键约束名无效

(6)当创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高),如果删除主键约束了,则自动删除主键约束对应的索引

(7)注意:不要修改主键字段的值,因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性

4、添加主键约束

(1)建表时指定主键约束

#列级模式
CREATE TABLE 表名称(
    字段名 数据类型 PRIMARY KEY,
    字段名 数据类型,
    字段名 数据类型
);

#表级模式
CREATE TABLE 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    [CONSTRAINT 约束名] PRIMARY KEY(字段名)
);

(2)建表后增加主键约束 

#字段列表可以是一个字段,也可以是多个字段,如果是多个字段的话,则是复合主键
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

5、删除主键约束 

ALTER TABLE 表名称 DROP PRIMARY KEY;

(1)删除主键约束,不需要指定主键名,因为一个表只有一个主键,删除主键约束后,非空还存在

6、添加主键约束时,往往需要设置字段自动增加属性

 

自增列

1、作用:某个字段的值自增

2、关键字:AUTO_INCREMENT 

3、特点、要求

(1)一个表最多只能有一个自增长列

(2)当需要产生唯一标识符或顺序值时,可设置自增长

(3)自增长列约束的列必须是键列(主键列,唯一键列)

(4)自增约束的列的数据类型必须是整数类型

(5)如果自增列指定 0 和 NULL,会在当前最大值的基础上自增;如果自增列手动指定具体值,直接赋值为具体值

(6)初始值默认从 1 开始,若在插入指定数值,则下一次增长从指定数值开始

4、指定自增约束

(1)建表时

CREATE TABLE 表名称(
    字段名 数据类型 PRIMARY KEY AUTO_INCREMENT,
);

CREATE TABLE 表名称(
    字段名 数据类型 UNIQUE KEY AUTO_INCREMENT,
);

(2)建表后

ALTER TABLE 表名称 MODIFY 字段名 数据类型 AUTO_INCREMENT;

5、删除自增约束

#去掉AUTO_INCREMENT,相当于删除
ALTER TABLE 表名称 MODIFY 字段名 数据类型;

6、MySQL 8.0 新特性:自增变量的持久化

(1)在 MySQL 8.0 之前,自增主键 AUTO_INCREMENT 的值如果大于 max(primary key)+1,在 MySQL 重启后,会重置 AUTO_INCREMENT =max(primary key)+1,这种现象在某些情况下,会导致业务主键冲突,或其他难以发现的问题

(2)在 MySQL 5.7 中,对于自增主键的分配规则,是由 InnoDB 数据字典内部一个计数器来决定的,而该计数器只在内存中维护 ,并不会持久化到磁盘中,当数据库重启时,该计数器会被初始化

(3)MySQL 8.0 将自增主键的计数器持久化到重做日志中,每次计数器发生改变,都会将其写入重做日志中,如果数据库重启,InnoDB 会根据重做日志中的信息来初始化计数器的内存值

 

外键约束

1、作用:限定某个表的某个字段的引用完整性

2、关键字:FOREIGN KEY

3、主表和从表 / 父表和子表

(1)主表(父表):被引用的表,被参考的表

(2)从表(子表):引用别人的表,参考别人的表

4、特点

(1)从表的外键列,必须引用主表的主键或唯一约束的列,因为被依赖的值必须是唯一的

(2)在创建外键约束时,如果不给外键约束命名,默认名不是列名,而是自动产生一个外键名,也可以指定外键约束名

(3)若创建表时就指定外键约束,则先创建主表,再创建从表

(4)删表时,先删从表(或先删除外键约束),再删除主表

(5)当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据

(6)在从表中指定外键约束,并且一个表可以建立多个外键约束

(7)从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致,如果类型不一样,创建子表时,就会出现错误:ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)

(8)当创建外键约束时,系统默认会在所在的列上建立对应的普通索引,索引名是外键的约束名(根据外键查询效率高)

(9)删除外键约束后,必须手动删除对应的索引

5、添加外键约束

(1)建表时

CREATE TABLE 主表名称(
    字段1 数据类型 PRIMARY KEY,
    字段2 数据类型
);

#FOREIGN KEY: 在表级指定子表中的列
#REFERENCES: 标示在父表中的列
CREATE TABLE 从表名称(
    字段1 数据类型 PRIMARY KEY,
    字段2 数据类型,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY(从表的某个字段) REFERENCES 主表名(被参考字段) [ON UPDATE 约束等级][ON DELETE 约束等级]
);

(2)建表后:一般情况下,表与表的关联都是提前设计好的,因此,会在创建表的时候就把外键约束定义好

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [ON UPDATE 约束等级][ON DELETE 约束等级];

6、约束关系针对双方

(1)添加外键约束后,主表的修改和删除数据受约束

(2)添加外键约束后,从表的添加和修改数据受约束

(3)在从表上建立外键,要求主表必须存在

(4)删除主表时,要求从表从表先删除,或将从表中外键引用该主表的关系先删除

7、约束等级

(1)CASCADE 方式:在父表上 UPDATE / DELETE 记录时,同步 UPDATE / DELETE 子表的匹配记录

(2)SET NULL 方式:在父表上 UPDATE / DELETE 记录时,将子表上匹配记录的列设为 NULL,但子表的外键列不能为 NOT NULL

(3)NO ACTION 方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行 UPDATE / DELETE 操作

(4)RESTRICT 方式 :同 NO ACTION, 都是立即检查外键约束

(5)SET DEFAULT 方式:父表有变更时,子表将外键列设置成一个默认的值,但 InnoDB 不能识别(在 SQLyog 中可能显示空白)

(6)如果没有指定等级,就相当于 RESTRICT 方式

(7)对于外键约束,最好是采用:ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

8、删除外键约束

(1)查看约束名和删除外键约束

#查看某个表的约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

(2)查看索引名和删除索引(只能手动删除) 

#查看某个表的索引名
SHOW INDEX FROM 表名称;
ALTER TABLE 从表名 DROP INDEX 索引名;

9、开发

(1)不得使用外键与级联,一切外键概念必须在应用层解决

(2)MySQL 允许不使用系统自带的外键约束,在应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性

(3)外键与级联更新适用于单机低并发 ,不适合分布式、高并发集群

(4)级联更新是强阻塞,存在数据库更新风暴的风险

(5)外键影响数据库的插入速度

10、外键约束不能跨引擎使用

 

检查约束

1、作用:检查某个字段的值是否符合要求,一般指的是值的范围

2、关键字:CHECK

3、MySQL 5.7 不支持,只做语法校验,但没有任何作用,添加数据时,没有任何错误或警告

4、MySQL 8.0 可以使用

 

默认约束

1、作用:给某个字段指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值

2、关键字:DEFAULT

3、默认值约束不与唯一约束、主键约束搭配

4、添加默认值

(1)建表时

CREATE TABLE 表名称(
    字段名 数据类型 NOT NULL DEFAULT 默认值,
);

(2)建表后

ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值;
#如果这个字段原来有非空约束,若还保留非空约束,那么在加默认值约束时,保留非空约束,否则非空约束被删除;某个字段加非空约束同理
ALTER TABLE 表名称 MODIFY 字段名 数据类型 DEFAULT 默认值 NOT NULL;

5、删除默认值约束

#删除默认值约束,也不保留非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型;
#删除默认值约束,保留非空约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

6、NOT NULL DEFAULT 或 DEFAULT 0

(1)不让表中出现 NULL 值

(2)不易比较,NULL 是一种特殊值,比较时只能用专门的 IS NULL 和 IS NOT NULL 来比较,碰到运算符,通常返回 NULL

(3)效率不高,影响提高索引效果

posted @   半条咸鱼  阅读(260)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示