SQL-约束

# 约束
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