数据完整性约束
数据的完整性约束(简称“约束”)是在表和字段上强制执行的数据检测规则,是为了防止不规范的数据进入数据库。当我们对数据进行 DML 操作时,数据库管理系统(DBMS)会自动按照我们设置的约束条件对数据进行检测,以保证数据存储的完整性和准确性。
完整性约束分为 4 类:实体完整性约束、域完整性约束、参照完整行约束、用户自定义完整性约束。
- 实体完整性约束:用来标识表中的每一条记录都代表一个实体,如主键约束;
- 域完整性约束:用来针对单元格的约束,如非空约束等;
- 参照完整行约束:指多表之间的对应关系,在一张表中执行数据插入、更新、删除等操作时,DBMS 都会跟另一张表进行对照,避免不规范的操作,以确保数据存储的完整性,如外键约束;
- 用户自定义完整性约束:用户根据实际的要求来定义,在执行数据插入、更新等操作时,DBMS 会检查数据是否满足检查约束中的限定条件,避免不符合条件的操作,以保证数据存储的准确性,如检查约束(MySQL 不支持,Oracle 支持)。
在 MySQL 中支持的约束有 6 种,分别是:非空约束、主键约束、默认值约束、唯一约束、外键约束和自定义检查约束。
根据约束添加的位置,我们可以把约束分为两类:
- 列级约束:即直接在定义的字段名和类型后面追加约束。但该方式只支持默认值约束、非空约束、主键约束、唯一约束。
- 表级约束:即在各个字段定义完后进行添加。表级约束不支持非空约束和默认值约束。
非空约束
创建非空约束使用的语句是 NOT NULL,其作用是保证该字段不能为空
1.建完表然后添加
CREATE TABLE specialty (
specialty_name VARCHAR(20),
specialty_college VARCHAR(20)
);
ALTER TABLE specialty CHANGE COLUMN specialty_name specialty_name VARCHAR(20) NOT NULL; # 添加非空约束
2.建表时候添加
CREATE TABLE specialty (
specialty_name VARCHAR(20) NOT NULL; # 添加非空约束
specialty_college VARCHAR(20)
);
查看新建表结构:
DESC specialty;
输出结果:
在我们插入数据的时候,如果该字段值为空就会出错,如下所示:
INSERT INTO specialty VALUES (NULL, '计算机学院');
输出结果:
这里需要注意一点的是空值(NULL)与空字符串('')的区别。
- 查询判定的方式不同:判定某值是否为 NULL,可以使用
IS NULL
或者IS NOT NULL
;而判定某值是否空字符串,使用=
、<>
来运算。 - 是否参与运算:空值(NULL)是不参与运算的,而空字符串可以。
- 是否占用空间:空值(NULL)是占用空间的,而空字符串('')是不占用空间的。所以在设计表时,我们的字段尽可能设置为
NOT NULL
约束。
主键约束
创建主键约束使用 PRIMARY KEY
语句,其作用是保证该字段的值具有唯一性.每张表只能创建一个主键,是该表中的数据在表内的唯一标识,且默认自带非空属性。
1.利用修改表的方式,创建主键约束。
修改 student 表,使 student_id 成为主键,具体实现 SQL 语句如下:
ALTER TABLE student MODIFY student_id INT PRIMARY KEY;
2.创建时候添加
CREATE TABLE student (
student_id INT,
student_name VARCHAR(20),
student_specialty VARCHAR(20),
primary key (student_id)
);
查看表结构,Key 这一列出现 PRI 即设置成功:
DESC student;
输出结果:
主键设置成功后,插入数据就要考虑它的唯一性和非空性
主键不能重复,需要我们在插入数据的时候知道待插入记录的主键是否已经存在。数据少且有序比较好确认,如果数据多且乱序,确认主键重复的问题就会变得很麻烦。
有没有办法在我们插入记录的时候,让主键自动保持唯一性呢? 答案就是使用自增序列(auto_increment),一张表有且只能有一个自增序列。自增序列一般和主键搭配使用,使用自增序列字段的类型为整型。
修改 student 表的主键,
使 student_id 具有自增属性。使用 DROP 语句删除已经存在的主键特性,其实现 SQL 语句如下:
ALTER TABLE student DROP PRIMARY KEY;
输出结果:
再次设置 student_id 为主键,
且具有自增属性。其 SQL 语句如下:
ALTER TABLE student MODIFY student_id INT PRIMARY KEY AUTO_INCREMENT;
使用自增长的好处是不用担心主键会重复的问题。需要注意,在插入数据的时候,自增长字段的值使用 NULL 来代替
默认值约束
创建默认值约束使用的关键字是 DEFAULT,其作用保证该字段总会有值..设置所有学生的专业默认是大数据专业,其 SQL 语句如下:
ALTER TABLE student MODIFY student_specialty VARCHAR(20) DEFAULT '大数据专业';
下列语句不给 student_specialty 字段指定值:
INSERT INTO student (student_id, student_name) VALUES (null, '小蓝');
因为默认值约束的存在,所以即便不给 student_specialty 字段指定值,系统也会自动插入默认值。查看插入后的结果如下:
SELECT * FROM student;
输出结果:
唯一约束
创建唯一约束使用的关键字是 UNIQUE
,其作用保证字段值的唯一性。唯一约束和主键约束的相同点都是保证值的唯一性。区别在于,唯一约束在一张表中允许出现多个,而主键约束只能有一个
查看它的表结构:
DESC specialty;
操作截图如下:
现在执行如下语句将 specialty_name 这一列增加唯一约束,使其不得有重复值出现:
ALTER TABLE specialty ADD CONSTRAINT UNIQUE(specialty_name);
再次查看其表结构:
DESC specialty;
操作截图:
如上图所示,specialty_name 这一行的 Key 有了一个 PRI
,这是主键的标识。
非空约束 + 唯一约束 = 主键。
外键约束
创建外键约束使用的关键字是 FOREIGN KEY
,其作用为限制两个表的关系,保证表中该字段的值来自于关联表。外键约束必须使用表级约束的方式来定义。
现在执行如下语句,将学生表中的 student_specialty 设置为外键,关联 specialty 表中的 specialty_name :
ALTER TABLE student ADD FOREIGN KEY (student_specialty) REFERENCES specialty(specialty_name);
输出结果:
关键字 REFERENCE
连接关联的表。需要注意,主表在插入记录的时候,外键的值必须能在关联表的关联列中找到,否则插入信息失败。
mysql函数里面的format使用参考 MySql不难,我们一起进步之函数篇 - 掘金 (juejin.cn)
format为转换的格式,包含格式如下:
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss )
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天