MuSQL （三）主键、外键、唯一、检查、默认值、非空约束总结及索引

1.主键约束

用于确定每行数据的标识符，唯一、不重复、非空

每一个表只能定义一个主键

复合主键不能包含不必要的多余列

一个列名只能在复合主键列表中出现一次

#创建表时添加主键
use test; 
create table  `content`  
(`id` int(10), 
`name` varchar(20),
`department` int(10),
`salary` float,
primary key(`id`) #设置主键
)engine = InnoDB auto_increment = 01 default charset = utf8mb4 collate = utf8mb4_bin;

#创建表时添加复合主键
use test; 
create table  `content2`  
(`id` int(10), 
`name` varchar(20),
`department` int(10),
`salary` float,
primary key(`id`,`department`) #设置复合主键
)engine = InnoDB auto_increment = 01 default charset = utf8mb4 collate = utf8mb4_bin;

#修改表时添加主键
use test; 
create table  `content3`  
(`id` int(10), 
`name` varchar(20),
`department` int(10),
`salary` float
)engine = InnoDB auto_increment = 01 default charset = utf8mb4 collate = utf8mb4_bin;

alter table content3 
add primary key(id); #设置主键

2.外键约束

父表必须已经存在与数据库中，并定义主键

父表为当前正在创建的表时，父表和子表应为同一张表（自参照表）

主键不可为空，外键可为空

外键中列的数量与类型需与父表主键一致

create database test;
use test;
create table  `total`  #创建父表
(`id` int(10), 
`name` varchar(20),
`department` int(10),
`salary` float,
primary key(`id`) #设置主键 无,
)engine = InnoDB auto_increment = 01 default charset = utf8mb4 collate = utf8mb4_bin;


#创建时添加外键约束
use test;
create table  `section`  #创建子表
(`id` int(10) primary key,  #设置主键
`name` varchar(20),
`deptId` int(10),
`salary` float,
constraint sec1 #设置外键约束名称  无,
foreign key(deptId) references total(id) #子表中的外键约束deptId，连接到父表total中的id
)engine = InnoDB auto_increment = 01 default charset = utf8mb4 collate = utf8mb4_bin;

#创建后添加外键约束
use test;

create table  `section2`  #创建子表
(`id` int(10) primary key,  #设置主键
`name` varchar(20),
`deptId` int(10),
`salary` float
)engine = InnoDB auto_increment = 01 default charset = utf8mb4 collate = utf8mb4_bin;

alter table section2
add constraint sec2 #设置外键约束名称 
foreign key(deptId) #子表中的外键约束deptId
references total(id) #连接到父表total中的id

举例：

#准备数据
create database test;
use test;
create table  total  #创建父表
(`id` int primary key, 
`school_name` varchar(20)
)engine = InnoDB default charset = utf8mb4 collate = utf8mb4_bin;

insert into `total` values(1,'CUMT');
insert into `total` values(2,'JNU');
insert into `total` values(3,'BCD');

create table  section  #创建子表
(`id` int primary key,  #设置主键
`deptId` int,
`salary` int
)engine = InnoDB default charset = utf8mb4 collate = utf8mb4_bin;


insert into `section` values(1,1,18);
insert into `section` values(2,2,23);
insert into `section` values(3,3,20);
insert into `section` values(4,1,20);
insert into `section` values(5,2,25);
insert into `section` values(6,3,22);

父表如下：

子表如下：将子表的deptId链接到父表的id，意味着子表中deptId列的1表示学校名称为CUMT

#插入外键：父表删除更新，子表随着删除更新
alter table section
add constraint sec  
foreign key(deptId) 
references total(id) 
on update cascade on delete cascade; #外键删除更新设置

#将'BCD'前面的序号变为4
use test;
update test.total
set id = 4
where school_name = 'BCD'

父表：

子表：

#删除'BCD'
use test;
delete from test.total
where school_name = 'BCD'

父表：

子表：

#插入外键：父表删除更新，子表用null
alter table section
add constraint sec  
foreign key(deptId) 
references total(id) 
on update set null on delete set null; #外键更新删除设置

子表：无论父表删除或是更新，子表均如下

#删除外键约束
use test;
alter table section2
drop foreign key sec2;

其中，PRI为主键约束，MUL为主键约束

3.唯一约束

要求该列所有数据唯一，可为空（只能出现一个空值）

#创建时设置唯一约束
use test;
create table  `content`  
(`id` int(10) primary key, 
`first_name` varchar(20) unique, #设置唯一约束
`last_name` varchar(20)
)engine = InnoDB auto_increment = 01 default charset = utf8mb4 collate = utf8mb4_bin;


#创建后设置唯一约束
use test;
alter table content
add constraint unique_name unique(last_name); #设置唯一约束


#删除唯一约束
use test;
alter table content
drop index unique_name; #删除唯一约束

其中，UNI为唯一约束

4.检查约束

判断输入数据是否复合条件，

可扩展到基于表的检查约束

#创建时设置检查约束
use test;
create table total  
(`id` int(10) primary key, 
`name` varchar(20),
`department` int(10),
`salary` float,
check(salary > 0 and salary < 10000) #设置检查约束，未设定检查约束名称
#constraint  `check_salary` check(salary > 0 and salary < 10000) #设置检查约束，检查约束名称为`check_salary`
)engine = InnoDB auto_increment = 01 default charset = utf8mb4 collate = utf8mb4_bin;


#插入数据
insert into total(id,name,department,salary) values(1,'A',100,1000); 
insert into total(id,name,department,salary) values(1,'A',100,1000000); #超出范围报错


#创建后设置检查约束
alter table total
add constraint check_id check(id >0) #设置检查约束，检查约束名称为check_id

运行删除检查约束代码，并没有任何效果，

部分文章说MySQL Workbench5.7不支持检查约束，所以可能有一部分缺陷。

5.默认值约束

使某一列为默认值

#创建时添加
use test;
create table total  
(`id` int(10) primary key, 
`name` varchar(20),
`department` int(10),
`salary` float default 100.00 #设置默认值
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;


#创建后
alter table total
change column salary
salary int default 100;


#删除约束
alter table total 
change column salary
salary int default null;

6.非空约束

确保输入的数据非空值，为空值时报错

#创建时
use test;
create table total  
(`id` int(10) primary key, 
`name` varchar(20),
`department` int(10),
`salary` float not null
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;


#创建后
alter table total
change column salary
salary int not null;


#删除约束
alter table total 
change column salary
salary int null;

7.查看表的约束

show create table total;

8.总结

约束名称	创建时	创建后	删除
主键约束	`id` int(10) primary key primary key(`id`) primary key(`id`,`department`)	alter table content3 add primary key(id);
外键约束	constraint sec1 foreign key(deptId) references total(id)	alter table section2 add constraint sec2 foreign key(deptId) references total(id)	alter table section2 drop foreign key sec2;
唯一约束	`first_name` varchar(20) unique,	alter table content add constraint unique_name unique(last_name);	alter table content drop index unique_name;
检查约束	check(salary > 0) constraint `check_s` check(salary > 0)	alter table total add constraint check_id check(id >0)
默认值 约束	`salary` float default 100.00	alter table total change column salary salary int default 100;	alter table total change column salary salary int default null;
非空约束	`salary` float not null	alter table total change column salary salary int not null;	alter table total change column salary salary int null;

9.索引

主键和外键均属于索引

索引从1开始

create database test;

#创建时设置索引
use test;
create table total  
(`id` int(10) primary key, 
`name` varchar(20),
`dept` int(10),
`height` float ,
index h(height) #设置索引,index 索引名(列名)
#unique index(height) #设置唯一索引
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

create database test;

#创建后设置索引
use test;
create table total  
(`id` int(10) primary key, 
`name` varchar(20),
`dept` int(10),
`height` float
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

create index h on test.total(height); #create index 索引名 on 库名.表名(列名)
create index n_h on test.total(name,height); #联合索引

#查看索引
show indexes from total in test; #from 表名 in 库名

#删除索引
drop index height on total;  #索引名 on 表名

alter table total
drop index height;