04.约束
1. 概述#
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确、有效性和完整性。
分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本以后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致 | FOREIGN KEY |
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
需求: 根据需求,完成表结构的创建。
字段名 字段含义 字段类型 约束条件 约束关键字 id
ID唯一标识 INT
主键,并且自动增长 PRIMARY KEY,AUTO_INCREMENT name
姓名 VARCHAR(10)
不为空,并且唯一 NOT NULL,UNIQUE age
年龄 INT
大于0,并且小于等于120 CHECK status
状态 CHAR(1)
如果没有指定该值,默认为1 DEFAULT gender
性别 CHAR(1)
无 CREATE TABLE tb_user( `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT "ID唯一标识", `name` VARCHAR(10) NOT NULL UNIQUE COMMENT "姓名", `age` INT CHECK(age > 0 && age <= 120) COMMENT "年龄", `status` CHAR(1) DEFAULT "1" COMMENT "状态", `gender` CHAR(1) COMMENT "性别" );
插入数据
insert into tb_user(name,age,status,gender) values ('Tom1',19,'1','男'),('Tom2',25,'0','男'); insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男'); insert into tb_user(name,age,status,gender) values (null,19,'1','男'); insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男'); insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男'); insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男'); insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男'); insert into tb_user(name,age,gender) values ('Tom5',120,'男');
2. 外键约束#
4.3.1 介绍#
外键:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
左侧的emp
表是员工表,里面存储员工的基本信息,包含员工的ID
、姓名
、年龄
、职位
、薪资
、入职日期
、上级主管ID
、部门ID
,在员工的信息中存储的是部门的ID dept_id
,而这个部门的ID
是关联的部门表dept
的主键id
,那emp
表的dept_id
就是外键,关联的是另一张表的主键
注意:目前上述两张表,只是在逻辑上存在这样一层关系;在数据库层面,并未建立外键关联,所以是无法保证数据的一致性和完整性的。
准备数据
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`(
`id` INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
`name` VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT "部门表";
INSERT INTO
`dept`(id, name)
VALUES
(1, '研发部'),
(2, '市场部'),
(3, '财务部'),
(4,'销售部'),
(5, '总经办');
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`(
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT "唯一标识",
`name` VARCHAR(50) NOT NULL COMMENT "姓名",
`age` INT COMMENT "年龄",
`job` VARCHAR(20) COMMENT "职位",
`salary` INT COMMENT '薪资',
`entrydate` DATE COMMENT '入职时间',
`managerid` INT COMMENT '直属领导ID',
`dept_id` INT COMMENT '部门ID'
) COMMENT "员工表";
INSERT INTO
emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);
这样一来,删除任何一个部门都可以正常删除,没有保护机制。
而删除成功之后,部门表不存在id为1的部门,而在emp表中还有很多的员工,关联的为id为1的部门,此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。
4.3.2 语法#
-
添加外键
-- 创建表的时候添加 CREATE TABLE 表名( 字段名 数据类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) );
-- 修改的方式添加 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
为
emp
表的dept_id
字段添加外键约束,关联dept
表的主键id
。ALTER TABLE `emp` ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);
添加了外键约束之后,再到
dept
表(父表)删除id
为1的记录,然后看一下会发生什么现象。 此时将会报错,不能删除或更新父表记录,因为存在外键约束。 -
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除
emp
表的外键fk_emp_dept_id
。ALTER TABLE `emp` DROP FOREIGN KEY `fk_emp_dept_id`;
4.3.3 删除/更新行为#
添加了外键之后,再删除父表数据时产生的约束行为,就称为删除/更新行为。具体的删除/更新行为有以下几种:
行为 | 说明 |
---|---|
NO ACTION |
当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为 |
RESTRICT |
当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为 |
CASCADE |
当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL |
当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null)。 |
SET DEFAULT |
父表有变更时,子表将外键列设置成一个默认的值 (Innodb不支持) |
具体语法为:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
NO ACTION
ALTER TABLE `emp` ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE NO ACTION ON DELETE NO ACTION;
RESTRICT
ALTER TABLE `emp` ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE NO RESTRICT ON DELETE NO RESTRICT;
CASCADE
ALTER TABLE `emp` ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE NO CASCADE ON DELETE NO CASCADE;
原来在子表中dept_id值为1的记录,现在也变为6了,这就是cascade级联的效果。
SET NULL
ALTER TABLE `emp` ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE SET NULL ON DELETE SET NULL;
SET DEFAULT
ALTER TABLE `emp` ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE SET DEFAULT ON DELETE SET DEFAULT;
作者:ChanceySolo
出处:https://www.cnblogs.com/chancey/p/17245986.html
版权:本作品采用「ChanceySolo-非商业性使用-相同方式共享 4.0 国际」许可协议进行许可。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性