12.约束

一：添加约束

1.在创建表时添加约束

create table student
(
    id int primary key,
    name varchar(10) not null,
    age int check(age between 1 and 120),
    sex varchar(8) not null check(sex in ('male','female')),
    IDCard varchar(18) unique,
    class_id int,
    foreign key (class_id) references class (c_id)
)charset=utf8;

create table class
(
    c_id int primary key,
    c_name varchar(20) not null,
    c_info varchar(200)
)charset=utf8;

2.查看表所有信息包括约束

show create table t_student;

3.为约束指定名称

create table student
(
    id int,
    name varchar(10) not null,
    age int,
    sex varchar(8),
    IDCard varchar(18),
    class_id int,
    constraint pk_id primary key (id),
    constraint ck_age check(age between 1 and 120),
    constraint ck_sex check(sex in ('male','female')),
    constraint uq_IDCard unique (IDCard),
    constraint fk_class_id foreighn key (class_id) references class(c_id)
)charset=utf8;

4.在创建表后再添加约束

create table student
(
    id int,
    name varchar() not null,
    age int,
    sex varchar(8),
    IDCard varchar(18),
    class_id int 
);

alter table student add constraint pk_id primary key (id);
alter table student add constraint ck_age check(age between 1 and 120);
alter table student add constraint ck_sex check(sex in ('male','female'));
alter table student add constraint uq_IDCard unique(IDCard);
alter table student add constraint fk_class_id foreign key (class_id)
references class (c_id);

二：删除约束

1.删除主键约束

alter table 表名 drop primary key

2.删除外键约束

alter table 表名 drop foreign key 约束名称

3.删除唯一约束

alter table 表名 drop index 约束名称

4.删除非空约束

alter table 表名 modify 列名 数据类型 null

三：常见五种约束：主键，非空，检查，唯一，外键

create table student
(
   id int primary key,
   name varchar(10) not null,
   age int check(age between 1 and 120),
   sex varchar(8) not null check(sex in ('male','female')),
   IDCard varchar(18) unique,
   class_id int,
   foreign key (class_id) references class (c_id) on delete cascade
)charset=utf8;