# 约束
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 '用户表';
insert into user (name,age,status,gender)
values ('Tom1','19','1','男'),('Tom2','18','0','女');
insert into user (name,age,status,gender)values ('Tom3','19','1','男');
insert into user (name,age,status,gender)values (null,'19','1','男');# Column 'name' cannot be null
insert into user (name,age,status,gender)values ('Tom3','19','1','男');# Duplicate entry 'Tom3' for key 'user.name'
insert into user (name,age,status,gender)values ('Tom4','110','1','男'); #此时主键为5,原因前面虽然失败但是向数据库申请了主键4.
insert into user (name,age,status,gender)values ('Tom5','130','1','男');# Check constraint 'user_chk_1' is violated.
insert into user (name,age,gender)values ('Tom5','110','男');
# 外键 dept id 与 user dept_id 关联,保证数据的一致性和完整性
# 外键约束
# 添加外键
alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept(id);
delete from dept where id =1; #a foreign key constraint fails (`itcast`.`user`, CONSTRAINT `fk_user_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
# 删除外键
alter table user drop foreign key fk_user_dept_id;
# 删除更新行为
# MySQL不支持set deafault
alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;# cascade同步更新删除
alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;# 删除涉及设置为null