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 介绍#

外键:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。

image-20230316223610041

左侧的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);

image-20230316224957211

这样一来,删除任何一个部门都可以正常删除,没有保护机制。

而删除成功之后,部门表不存在id为1的部门,而在emp表中还有很多的员工,关联的为id为1的部门,此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。

4.3.2 语法#

  1. 添加外键

    -- 创建表的时候添加
    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);
    

    image-20230316231343696

    添加了外键约束之后,再到dept表(父表)删除id为1的记录,然后看一下会发生什么现象。 此时将会报错,不能删除或更新父表记录,因为存在外键约束。

    image-20230316232141884

  2. 删除外键

    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;

image-20230321214211728

原来在子表中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 国际」许可协议进行许可。

posted @   ChanceySolo  阅读(12)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
more_horiz
keyboard_arrow_up light_mode palette
选择主题
menu
点击右上角即可分享
微信分享提示