12. constraint

One ： Adding constraints

1. Add constraints when creating tables

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. View all information in the table, including constraints

show create table t_student;

3. Specify a name for the constraint

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. Add constraints after creating the table

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);

Two ： Delete constraints

1. Delete primary key constraint

alter table  Table name  drop primary key 

2. Delete foreign key constraint

alter table  Table name  drop foreign key  Constraint name 

3. Delete unique constraint

alter table  Table name  drop index  Constraint name 

4. Delete non empty constraints

alter table  Table name  modify  Name   data type  null

3、 ... and ： Five common constraints ： Primary key , Non empty , Check , only , Foreign keys

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;