1.4.3 SQL基础 - DDL语言 - 常见约束

1.4.3 SQL基础 - DDL语言 - 常见约束

常见约束

含义

一种限制、用于限制表中的数据，为了保证表中的数据的准确性与可靠性

create table 表名(
		字段名  字段类型  约束
)；

分类

- 六大约束：
		NOT NULL：非空、用于保证该字段的值不能为空，比如性别、学号等
		DEFAULT：默认、用于保证该字段有默认值；比如性别
		PRIMARY KEY：主键，保证该字段的值有唯一性，并且非空，比如学号、员工编号
		UNIQUE：唯一，用于保证该字段的值具有唯一性、可以为空
		CHECK：检查约束，【MySQL不支持】，比如年龄、性别
		FOREIGN KEY：外键约束、用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值；
			在从表添加外键约束、用于引用主表中某列的值
			比如：学生表的专业编号、员工表的部门编号、员工表的工种编号

- 添加约束的时机：
	
		1、创建表时
		2、修改表时
	
- 约束的添加分类：
	
		列级约束：
				六大约束语法上都支持，但外键约束没有效果
		表级约束：
				除了非空、默认，其他的都支持

- 主键和唯一的对比

				保证唯一性   是否为空		一个表中可以有多少个		是否允许组合
		主键		√			×		至多一个					允许，但不推荐
		唯一		√			√		可以有多个				不允许，但不推荐

- 外键
 	1. 要求在从表设置外键关系
 	2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容
 	3. 主表的关联列必须是一个 key (一般是主键或唯一)
 		插入数据时应该先插入主表，再插入从表
 		删除数据时，先删除从表，在删除主表

- 列级约束与表级约束
				位置		支持的约束类型					是否可以其约束名
 列级约束 		列的后面		语法都支持，但外键没有效果		不可以
 表级约束		列的下面		默认和非空不支持，其他都支持		可以（主键没有效果）

一、创建表时添加约束

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;

2、添加表级约束

在各个字段的最下面

【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)  #外键
);

desc 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,

primary key(id),   	#主键
unique(seat),   		#唯一键
check (gender='男' or gender='女'),		#检查约束
foreign key(majorId) references major(id)  #外键
);

desc stuinfo;
show index from stuinfo;

3. 通用的写法

drop table  if exists stuinfo;
create table if not exists stuinfo(
		id int primary key ,  #主键
		stuName varchar(20)  not null,  #非空
		gender char(1),
		seat int unique ,  #唯一
		age int default 18, # 默认约束
		majorId int,        

        constraint fk_stuinfo_major foreign key(majorId) references major(id)  #外键
);

desc stuinfo;
show index from stuinfo;

二、修改表时添加约束

1、添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型(字段名)【外键的引用】;

1.添加非空约束

drop table  if exists stuinfo;
create table stuinfo(
		id int,
		stuname varchar(20),
		gender char(1),
		seat int,
		age int, 
		majorid int

);

添加非空约束

alter table stuinfo modify column stuname varchar(20) not null;

删除约束（空或null）

alter table stuinfo modify column stuname varchar(20) [null];

2、添加默认约束

alter table stuinfo modify column age int default 18;

3. 添加主键

列级约束

alter table stuinfo modify column id int primary key ;

表级约束

alter table stuinfo add [constraint 约束名] 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) ;

三、修改表时删除约束

1. 删除非空约束

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

或

alter table stuinfo modify column stuname varchar(20);

2. 删除默认约束

alter table stuinfo modify column age int;

3. 删除主键

alter table stuinfo modify column id int;

或

alter table stuinfo drop primary key;

4. 删除唯一

alter table stuinfo modify column seat int;

或

alter table stuinfo drop index seat；

5. 删除外键

alter table stuinfo drop  foreign key fk_stuinfo_major;

四、标识列

又称为自增长列

含义

可以不用手动的插入值，系统提供默认的序列值

特点

1、标识列必须要求是 key；
2、一个表中至多一个标识列；
3、标识列的类型只能是数值型；
4、标识列可以通过 set auto_increment_increment=3; 设置步长；可以通过手动插入值设置起始值

1. 创建表时设置标识列

 drop table  if exists tab_identity;
create table  tab_identity(
id int primary key auto_increment,
name varchar(20)
);

insert into tab_identity values(null, 'john');
# insert into tab_identity(name) values('john');
select * from tab_identity;

更改步长

set auto_increment_increment=3;

更改初始值
第一居插入语句

insert into tab_identity values(10, 'john');

1. 修改表时设置标识列

drop table  if exists tab_identity;
create table  tab_identity(
		id int ,
		name varchar(20)
);
alter table tab_identity modify column id int primary key auto_increment;
desc tab_identity;

2. 修改表时删除标识列

alter table tab_identity modify column id int;