03-约束.md

约束

---

use `java_basic`;
drop table if exists `student`;
create table if not exists `student`
(
    `id`         int auto_increment primary key                               not null comment '编号',
    `name`       varchar(32) check ( length(`name`) >= 1 )                    not null comment '姓名',
    `gender`     tinyint unsigned default 0 check ( `gender` in (0, 1, 2) )   not null comment '性别',
    `age`        tinyint unsigned default 0 check ( `age` between 0 and 150 ) not null comment '年龄',
    `entry_time` datetime                                                     null comment '入学时间'
) comment '学生表';

insert into student (`name`, `gender`, `age`, `entry_time`) value ('student01', 1, 19, now());
insert into student (`name`, `gender`, `age`, `entry_time`) value ('student02', default, 200, now()); -- fail
insert into student (`name`, `gender`, `age`, `entry_time`) value ('', default, default, now()); -- fail
insert into student (`id`, `name`, `gender`, `age`, `entry_time`) value (1, 'student01', default, default, null);

select *
from `student`;

drop table if exists `score`;
create table if not exists `score`
(
    `student_id` int unique,
    `chinese`    int check ( `chinese` between 0 and 100) comment '语文',
    `math`       int check ( `math` between 0 and 100) comment '数学',
    `english`    int check ( `english` between 0 and 100) comment '英语',
    constraint `student_id_fk1` foreign key (`student_id`) references `student` (`id`)
) comment '学生分数表';

insert into `score` (`student_id`, `chinese`, `math`, `english`)
values (1, 100, 100, 100);

select *
from `score`;

delete
from `student`
where `id` = 1;

alter table `score`
    drop foreign key `student_id_fk1`;

alter table `score`
    add constraint `student_id_fk1`
        foreign key (`student_id`) references `student` (`id`) on update cascade on delete cascade;

关键字	描述
not null	不为 null
unique	唯一约束
primary key	主键约束
default	默认约束
check	检查约束
foreign key	外键约束