Mysql基础（五）约束

版权申明：本文用于个人学习记录，学习课程为黑马程序员的mysql教程。如需获取官方的学习视频和文档资料，请至黑马程序员官方获取。下面附上教学视频的链接地址，向提供免费教学视频的老师致敬，学如逆水行舟，不进则退，共勉

黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibili

1、概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

2、演示

 对应的建表语句为：

CREATE TABLE user
(
    id     int AUTO_INCREMENT PRIMARY KEY 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 '性别'
) comment '用户表';

insert into user(name, age, status, gender) values ('Tom1', 19, '1', '男'),('Tom2', 25, '0', '男');
insert into user(name, age, status, gender) values ('Tom3', 19, '1', '男');

select * from user;

-- name 有非空约束not null
-- 执行报错，[23000][1048] Column 'name' cannot be null
insert into user(name, age, status, gender) values (null, 19, '1', '男');
-- name 有唯一约束UNIQUE
-- 执行报错，[23000][1062] Duplicate entry 'Tom3' for key 'user.name'
insert into user(name, age, status, gender) values ('Tom3', 19, '1', '男');

insert into user(name, age, status, gender) values ('Tom4', 80, '1', '男');

-- age有检查约束，check条件为大于0小于120
-- 执行报错，[HY000][3819] Check constraint 'user_chk_1' is violated.
insert into user(name, age, status, gender) values ('Tom5', -1, '1', '男');
-- 执行报错，[HY000][3819] Check constraint 'user_chk_1' is violated.
insert into user(name, age, status, gender) values ('Tom5', 121, '1', '男');

-- status有默认约束，default为1
insert into user(name, age, gender) values ('Tom5', 120, '男');

 图形化界面创建约束：

3、外键约束

 

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, '总经办');

create table emp(
    id int auto_increment comment 'ID' primary key,
    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);

1）添加外键

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的记录，然后看一下会发生什么现象。 此时将会报错，不能删除或更新父表记录，因为存在外键约束。

 2）删除外键

 ALTER  TABLE  表名  DROP  FOREIGN KEY  外键名称;

-- 删除外键
-- 删除emp表的外键fk_emp_dept_id。
alter table emp drop foreign key fk_emp_dept_id;

 3）删除/更新行为

具体语法为：

 ALTER   TABLE   表名   ADD   CONSTRAINT   外键名称   FOREIGN KEY (外键字段) REFERENCES  主表名 (主表字段名)  ON UPDATE CASCADE ON DELETE CASCADE;

-- 外键的删除和更新行为
-- cascade: 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
alter table emp add constraint  fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;

-- SET NULL: 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取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;