学习参考https://www.bilibili.com/video/BV1Kr4y1i7ru?spm_id_from=333.999.0.0
目录
一.概述
二.演示
1.创建表
create table user(
id int primary key auto_increment comment'主键',
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 '用户表';
2.插入数据
(1)验证auto_increment
insert into user(name, age, status, gender) values
('tom',19,1,'男'),
('jack',19,0,'男'),
('jack2',19,1,'男');
可以看出我们并没有插入id,但是id自动增长,
原因是我们在创建表的时候给了id一个约束关键字
auto_increment。
(2)验证not null
insert into user(name, age, status, gender) values
(null,19,1,'男');
name字段不能插入空值,
原因是创建表时给了name一个约束关键字
not null
(3)验证unique
insert into user(name, age, status, gender) values
('jack',19,0,'男');
name字段不能插入重复字符串,
原因是创建表时给了name一个约束关键字
unique
(4)验证check
insert into user(name, age, status, gender)
values ('jack4',100,0,'男');
insert into user(name, age, status, gender)
values ('jack4',121,0,'男');
insert into user(name, age, status, gender)
values ('jack4',-1,0,'男');
age字段不能插入超出范围的数据,
原因是创建表时给了age一个约束关键字
check(age > 0 && age <= 120)。
注意
但是为什么id是5呢,而不是4呢
name是unique
所以mysql会为其申请好主键4
尽管失败了,但4还是被申请走了。
第(3)步插入的是重复name,而为了唯一性是不会为他申请主键的。
(unique是申请完空间后的唯一判断)
insert into user(name, age, status, gender)
values ('jack5',120,0,'男');
(6)验证default
insert into user(name, age, gender)
values ('jack6',120,'男');
default 默认唯一(前面创建表的时候已经约束好了)
三.外键约束
1.添加外键
(1)创建父表并插入数据
create table animal(
id int primary key auto_increment comment '序号',
name varchar(10) comment '种类'
)comment '猫和老鼠';
insert into animal(name) values
('cat'),
('mouse');
(2)创建子表并插入数据
create table user(
id int primary key auto_increment comment'主键',
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'性别',
dept_key int comment '外键'
) comment '用户表';
insert into user(name, age, status, gender,dept_key) values
('tom',19,1,'男' ,1),
('jack',19,0,'男',2),
('jack2',19,1,'男',2);
(3)父表与子表相互关联
alter table user add constraint fk_user_dept_id foreign key(dept_key) references animal(id);
(4)验证
delete from animal where id = 1;
10:24:16 delete from animal where id = 1 Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`text`.`user`, CONSTRAINT `fk_user_dept_id` FOREIGN KEY (`dept_key`) REFERENCES `animal` (`id`)) 0.000 sec
2.删除外键
alter table user drop foreign key fk_user_dept_id;
四,外键删除更新行为
1.cascade
alter table user add constraint fk_user_dept_id foreign key(dept_key) references animal(id) on update cascade on delete cascade;
验证
update animal set id = 3 where name = 'cat';
delete from animal where id = 3;
2.set null
alter table user add constraint fk_user_dept_id foreign key(dept_key) references animal(id) on update set null on delete set null;
验证
delete from animal where id = 2;