MYSQL约束

https://blog.csdn.net/zhang_qings/article/details/81348197
非空约束：

create table bookinfo(
book_id int,
book_name varchar(20) not null
 );
 alter table bookinfo modify book_name varchar(20);
 alter table bookinfo modify book_name varchar(20)not null;

主键约束：

create table bookinfo(
    book_id int primary key,
    book_name varchar(20) not null
    );

create table bookinfo(
    -> book_id int ,
    -> book_name varchar(20) not null，
    -> constraint pk_id primary key(book_id)
    -> );

> create table bookinfo(
    -> book_id int,
    -> book_name varchar(20) not null,
    -> primary key(book_id,book_name)
    -> );

添加主键：

alter table bookinfo modify book_id primary key;
alter table bookinfo add primary key(book_id);
alter table bookinfo add constraint pk_id primary key(book_id);

删除主键：

alter table bookinfo drop primary key;

唯一约束：

create table bookinfo(
    book_id int primary key,
    book_name varchar(20) unique not null
    );

create table bookinfo(
    book_id int primary key,
    book_name varchar(20) not null,
    constraint uk_bname unique(book_name)
    );

添加唯一约束：

alter table bookinfo modify book_name varchar(20) unique;
alter table bookinfo add unique(book_name);
alter table bookinfo add constraint uk_bname unique(book_name);

删除唯一约束：

alter table bookinfo drop index book_name;
alter table bookinfo drop key book_name;

默认约束：

create table bookinfo(
    book_id int primary key,
    press varchar(20) default '机械工业出版社'
    );

修改默认约束：

alter table bookinfo modify press varchar(20) default '机械工业出版社';
alter table bookinfo alter column press set default '机械工业出版社';

删除约束：

alter table bookinfo modify press varchar(20);
alter table bookinfo alter column press drop default;

外键约束：

create table bookcategory(
    -> category_id int primary key,
    -> category varchar(20),
    -> parent_id int
    -> );
     create table bookinfo(
    -> book_id int primary key,
    -> book_category_id int,
    -> constraint fk_cid foreign key(book_category_id) references bookcategory(category_id)
    -> );

删除

alter table bookinfo drop foreign key fk_cid;

添加

mysql> alter table bookinfo add foreign key(book_category_id) references bookcategory(category_id);

级联

 create table bookinfo(
    -> book_id int primary key,
    -> book_category_id int,
    -> constraint fk_cid foreign key(book_category_id) references bookcategory(category_id) on delete cascade
    -> );

check约束：

create table xsxx(stuID varchar(11) not null,stuSex varchar(2) not null check(stuSex in("男","女")));