MySQL 约束

#常见约束

/*

NOT NULL：非空

         该字段的值是必填的，如果不设置该约束，该字段默认是可以为空

DEFAULT:默认

         该字段有默认值

CHECK:检查，mysql语法支持，但效果不支持

         该字段值可以加以限制，比如年龄可以控制在0-120之间

         

primary keY:主键

         该字段值不可以重复

         1、不可以为空

         2、一个表中只能有一个主键，但是可以多个字段做组合主键

unique:唯一约束

         该字段值不可以重复

         1、可以为空

         2、一个表中可以有多个唯一键，可以有组合唯一键

foreign key:外键

         用于限制多表的关系

         1、从表的该字段值必须来自于主表的关联列的值

         2、从表和主表的关联列的类型必须一样

         3、主表的关联列必须是主键

         4、在从表中设置外键

         5、一个表中可以添加多个外键

         6、插入数据时，先插入主表

            删除数据时，先删除从表

         

分类：

                          位置                 支持

         列级约束        列的后面        除了外键

         表级约束        表的后面        除了非空和默认

         

*/

 

#一、★★★★★★★★★★★创建表时添加约束 ★★★★★★★★★★★  √

#CREATE TABLE 表名(

#       字段名 字段类型 列级约束,

#       字段名 字段类型 列级约束,

#       表级约束）

 

#1.添加列级约束

use test;

drop table if exists stuinfo;

create table stuinfo(

         id
int not null unique,#非空约束+主键约束

         stuname
varchar(20) unique,#唯一约束

         gender
char default '男',#默认约束

         age
int unsigned check (age between 0 and 120)#检查约束

);

desc stuinfo;

select * from stuinfo;

 

 

#插入默认值

insert into stuinfo(id,stuname,age)
values(3,'lily',12);

insert into stuinfo
values(5,'jack',default,900);

 

 

#唯一和主键

#1.关于插入null

insert into stuinfo
values(7,null,default,900);

 

 

#2.关于插入多个唯一或主键

drop table if exists stuinfo;

create table stuinfo(

         id
int not null unique,#非空约束+主键约束

         stuname
varchar(20) unique,#唯一约束

         gender
char default '男',#默认约束

         age
int unsigned check (age between 0 and 120)#检查约束

);

 

 

2、表级约束

#添加表级约束

#语法

create table stuinfo(

         id
int ,

         stuname
varchar(20),

         gender
char,

         age
int unsigned,

         【constraint 约束名】约束类型(字段)

);

 

#案例

create table grade(

id int primary key,

gradename varchar(20)

)

drop table if exists stuinfo;

create table stuinfo(

         id
int not null,

         stuname
varchar(20),

         gender
char default '男',

         age
int unsigned,

         gradeid
int,

         primary
key(id,stuname),#组合主键：id+stuname

         constraint
uq unique(age),#唯一键

         constraint
fk_stuinfo foreign key(gradeid) references grade(id)#外键约束

);

 

desc stuinfo;

 

select * from grade;

#①组合主键

insert into grade values(1,'一年级'),(2,'二年级'),(3,'三年级');

insert into stuinfo values(2,'lucy','男',233,1);

 

#②外键

insert into stuinfo values(3,'lucy','男',13,10);

 

 

3、修改表时添加约束

 

#★★★★★★★★★★★修改表时添加约束 ★★★★★★★★★★★

 

#案例：

drop table if exists stuinfo;

create table stuinfo(

         id
int ,

         stuname
varchar(20),

         gender
char,

         age
int unsigned,

         gradeid
int

);

desc stuinfo;

 

#1.添加非空

Alter table stuinfo modify column gender
char not null; 

 

#2.添加默认

alter table stuinfo modify column age int
unsigned default 18;

 

#3.添加主键

#①方式一：列级约束的做法

alter table stuinfo modify column id int
primary key;

 

#②方式二：表级约束的做法

#语法：

#alter table 表名 add [constraint 约束名]  约束类型 (字段);

 

#案例：

alter table stuinfo add primary key(id);

 

#4.添加唯一

 

#①方式一：列级约束的做法

alter table stuinfo modify column stuname
varchar(20) unique;

 

#②方式二：表级约束的做法

alter table stuinfo add constraint
uq_stuinfo unique(stuname);

 

#5.添加外键

alter table stuinfo add constraint
fk_stuinfo_grade foreign key(gradeid) references grade(id);

 

4、修改表时删除约束

##三、★★★★★★★★★★★修改表时删除约束 ★★★★★★★★★★★

#1.删除非空

alter table stuinfo modify column gender
char ;

 

#2.删除默认

alter table stuinfo modify column age int
unsigned;

 

#3.删除主键

alter table stuinfo modify column id int ;

alter table stuinfo drop primary key;

 

#4.删除唯一

alter table stuinfo modify column stuname
varchar(20);

alter table stuinfo drop index uq_stuinfo;

 

#查看指定表的所有索引

show index from stuinfo;

 

#5.删除外键

alter table stuinfo drop foreign key
fk_stuinfo_grade;

 

5、常见约束练习

##未建立数据库

 

#1.   向表emp2的id列中添加PRIMARY
KEY约束（my_emp_id_pk）

alter table emp2 add constraint
my_emp_id_pk primary key(id);

 

 

#2.   向表dept2的id列中添加PRIMARY
KEY约束（my_dept_id_pk）

alter table dept2 add constraint
my_edpt_id_pk primary key(id);

 

 

#3.   向表emp2中添加列dept_id，并在其中定义FOREIGN
KEY约束，与之相关联的列是dept2表中的id列。

alter table emp2 add constraint fk foreign
key (dept_id) references dept2(id)