一、常见约束
约束的含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确性和可靠性
六大约束分类:
not null : 非空 ,用于保证该字段的值不能为空(比如:姓名,学号)
default : 默认,用于保证该字段有默认值(比如:性别)
primary key : 主键,用于保证该字段的值具有唯一性,并且非空(比如:学号)
unique : 唯一,用于保证该字段的值具有唯一性,可以为空(比如:座位号)
check : 检查约束 【mysql不支持】
foreign key : 外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束,用于引用主表中某列的值(比如:学生专业编号)
添加约束的时机:创建表时 和 修改表时
约束的添加分类:
列级的约束: 六大约束语法上都支持,但外键约束没有效果
表级的约束: 除了非空、默认,其它的都支持
create table 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
);
1. 创建表时添加约束
- 添加列级约束
直接在字段名和类型后面追加 约束类型即可
只支持: 默认、 非空、 主键、 唯一
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
id int PRIMARY KEY, #主键
stuName VARCHAR(20) not NULL, #非空
gender char(1) CHECK(gender = "男" or gender = "女"), #检查
seat int UNIQUE, #唯一
age int DEFAULT 18, # 默认约束
majorId INT REFERENCES major(id) #外键约束
);
CREATE TABLE major(
id int primary KEY , #主键
majorName VARCHAR(20)
);
DESC stuinfo;
#查看stuinfo表中所有的索引,包括主键 外键
show index from stuinfo;
- 添加表级约束
语法:
在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
drop table if exists stuinfo;
create table stuinfo(
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int,
constraint pk primary key(id), #主键
constraint uq unique(seat),
constraint ck check(gender = "男" or gender = "女"), #检查约束
constraint fk_stuinfo_major foreign key(majorid) references major(id) #外键
);
show index from stuinfo;
通用的写法:
create table if not exists stuinfo(
id int primary key,
stuname varchar(20) not null,
sex char(1),
age int default 18,
seat int unique,
majorid int,
constraint fk_stuinfo_major foreign key(majorid) references major(id) #外键
);
2. 主键和唯一键对比
- 主键:保证唯一性,不允许为空,一个表中至多有一个,允许多列组合成主键,但不推荐
- 唯一: 保证唯一性,允许为空,一个表中可以有多个,允许多列组合成唯一键,但不推荐
3. 外键的特点
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
- 主表的关联列必须是一个key(一般是主键或唯一键)
- 插入数据时,先插入主表,再插入从表,删除数据时,先删除从表,再删除主表
2. 修改表时添加约束
1. 添加的是列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2. 添加的是表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
drop table if exists stuinfo;
create table stuinfo(
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int
);
//1.修改表时添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
//2.添加默认约束
alter table stuinfo modify column age int default 18;
//3.添加主键
alter table stuinfo modify id int primary key; //列级约束
alter table stuinfo add primary key(id); //表级约束
//4.添加唯一键
alter table stuinfo modify column seat int unique; //列级约束
alter table stuinfo add unique(seat); //表级约束
//5.添加外键
alter table stuinfo add 【constraint fk_stuinfo_major】foreign key(majorid) references major(id);
3. 修改表时删除约束
//1.删除非空约束
alter table stuinfo modify column stuname varchar(20) null;
//2.删除默认约束
alter table stuinfo modify column age int;
//3.删除主键
drop table stuinfo drop primary key;
//4.删除唯一
alter table stuinfo drop index seat;
//删除外键
alter table stuinfo drop foreign key majorid;
二、自增长列(标识列)
auto_increment 含义:可以不用手动插入值,系统提供默认的序列值
特点:
① 自增长列不一定非要和主键搭配使用,但一定是一个key ,unique也可以
② 一个表中至多有一个自增长列
③ 标识列的类型只能是数值型(int double float)
④ 标识列可以通过 set auto_increment_increment = 3;设置步长,可以通过手动插入值,设置起始值
drop table if exists stuinfo;
create table stuinfo(
id int primary key auto_increment,
name varchar(20)
);
insert into stuinfo values(null,"join");
//显示自增长列的起始索引和补长
SHOW VARIABLES LIKE "%auto_increment%";
//设置自增长列的步长
SET auto_increment_increment = 3;
- 修改表时设置自增长列
alter table stuinfo modify column id int primary key auto_increment;
- 修改表时删除子自增长列
alter table stuinfo modify column id int primary key; //删除自增长列即可