第11章：约束

一、数据完整性与约束的分类

1.为什么需要约束constraint

SQL以约束方式对表数据进行额外的条件限制。

为了保证数据的完整性，SQL对表数据进行条件限制

①实体完整性：同一个表，不能存在两条相同无法区分的记录

②域完整性：年龄范围0-120，性别是男/女

③引用完整性：员工表员工所在的部门，要在部门表里找到

④用户自定义完整性：用户名唯一，密码不为空

2.什么是约束

约束是表级的强制规定

在创建表create table规定约束，创建表后alter table规定约束

3.约束的分类

角度1：约束的字段个数

单列约束 vs 多列约束

角度2：约束的作用范围

列级约束：将声明约束在对应字段的后面

表级约束：在表中所有字段声明完成后，在所有字段后面声明的约束

角度3：约束的作用

①not null 非空约束

②unique 唯一性约束

③primary key 主键约束

④foreign key 外键约束

⑤check 检查约束

⑥default 默认值约束

4.查看约束

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name='表名';

5.添加约束总结、

①

alter table ***

modify 字段名 数据类型  default/not null

②

alter table ***

add unique(字段名,字段名);

add primary key (字段名);

add foreign key (从表.字段) references 主表名(主表.字段);

二、非空约束not null

1.作用

限制值不能为空

 

2.特点

①默认情况下所有类型是NULL

②某个列单独为非空，不能组合列非空

③很多列可以设置非空

④空字符’’不是NULL，0也不是NULL

3.创建表添加非空not null 约束

 

CREATE TABLE test1 (

  id int(11) NOT NULL,

  last_name varchar(15) NOT NULL,

  email varchar(25),

  salary decimal(10,2)

)

 

4.已有表添加非空约束

①格式

alter table 表名称 modify 字段名 数据类型 not null;

②举例

alter table test1

modify email varchar(25) not null

5.删除约束

①格式

alter table 表名称 modify 字段名 数据类型

或者

alter table 表名称 modify 字段名 数据类型 null

②举例

alter table test1

modify email varchar(25) null

三、唯一性约束unique

1.作用

用来限制字段的值不能重复,但是可以为NULL，允许出现多个NULL

 

2.特点

①同一个表可以有多个唯一约束。

②唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。

③唯一性约束允许列值为空。

④在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

⑤MySQL会给唯一约束的列上默认创建一个唯一索引。

3.创建表添加唯一约束

 测试:id和email不能重复

create table test2(
id int unique, #列级约束
last_name varchar(15),
email varchar(15),
salary decimal(10,2),
# 表级约束
constraint uk_test2_email unique(email)
);

4.已有表添加唯一约束

alter table test2
modify salary decimal(10,2) unique

5.复合（多列）唯一性约束

创建表 name-password为组合约束

create table user(
id int,
name varchar(15),
password varchar(25),
# 表级约束
constraint uk_user_name_pwd unique(name,password)
);

测试 name和pawword看成整体

insert into user(id,name,password)
values
(1,'z','w'),
(2,'z','z')

6.删除唯一性约束：根据唯一索引删除

添加唯一性约束的时候，会自动创建唯一索引。删除根据索引名（跟约束名相同）删除。

alter table user

drop index uk_user_name_pwd

7.查看表的索引

show index from 表名称

四、主键约束primary key

1.作用

用来唯一标识表中的一行记录

2.关键字

primary key

3.特点：非空且唯一

 

①一个表里面有且仅有一个主键，可以存在复合主键

②复合主键的列都不为空，组合的值不重复

③主键名是primary,创建主键时会建立主键索引

4.新建表创建主键

 

①举例

create table temp(

id int primary key,

name varchar(20)

);

或

create table temp2(

id int,

name varchar(20),

primary key(id)

);

5.已有表添加主键

#字段列表是一个字段，也可以是多个字段

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

举例

ALTER TABLE student ADD PRIMARY KEY (sid);

ALTER TABLE emp5 ADD PRIMARY KEY(name,pwd);

6.复合主键

方式一：创建表添加复合主键

 方式二：已有表添加复合主键

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

7.删除主键约束

alter table 表名称 drop primary key;

删除主键约束，此时的非空还存在，只是不唯一了

五、自增列 auto_increment

1.作用

某个字段的值自增

2.关键字

auto_increment

3.特点

①一般加在主键/唯一性约束的后面

②类型必须是int

4.创建表时创建自增列

create table test7(
id int PRIMARY key AUTO_INCREMENT,
name varchar(15)
);

测试

省略自增列

insert into test7(name)

values

('wang'),

('zhang'),

('sun')

 如果自增列指定了 0 和 null，会在当前最大值的基础上自增

insert into test7(id,name)
values 
(0,'wang1'),
(null,'zhang1')

 如果自增列手动指定了具体值，直接赋值为具体值。

insert into test7(id,name)
values 
(8,'wang1')

5.修改表时创建自增列

alter table 表名称

modify 字段名 数据类型 auto_increment;

create table test8(
id int primary key,
name varchar(15)
);

alter table test8
modify id int auto_increment;

6.开发中遇到auto_increment字段时

不要给该字段赋值，省略过去

7.删除自增列auto_increment

alter table 表名称

modify 字段名 数据类型

alter table test8
modify id int

六、外键 foreign key

1.作用

限定表中的某个字段的引用完整性

比如：员工表所在的部门号，必须在部门表里有这个部门号。

2.关键字

foreign key

3.特点

①子表的外键必须引用父表的主键或唯一约束。因为添加外键时被引用父表的值是唯一的

②创建外键时，要先有主表

③删表时，先删除子表（有外键的表）在删除父表

④要删除父表的记录时（部门表）先删除子表（员工表）引用的该部门记录，然后才能删除成功。

⑤一个表可以有多个外键约束

⑥主键和外键的名称可以不一样，但是类型必须一样

⑦创建外键约束时，会在所在的列创建普通索引。删除外键约束，手动删除索引

4.创建表时添加外键

先创建主表

create table dept1(
dept_id int primary key,
dept_name varchar(15)
);

再创建子表

create table emp1(
id int primary key,
name varchar(15),
dept_id int,
constraint fk_emp1_dept_id foreign key(dept_id) references dept1(dept_id)
);

5.已有表时添加外键

先创建父表

create table dept2(
dept_id int primary key,
dept_name varchar(15)
);

再创建子表

create table emp2(
id int primary key,
name varchar(15),
dept_id int
);

alter table emp2

add foreign key (dept_id) references dept2(dept_id)

6.错误演示

①主表的dept_id没有主键或唯一性约束

create table dept3(

dept_id int,

dept_name varchar(15)

);

create table emp3(

id int primary key,

name varchar(15),

dept_id int,

constraint fk_emp1_dept_id foreign key(dept_id) references dept1(dept_id)

);

7.演示外键增删改

①部门表

 ②增加员工表(部门号是部门表已经有的，不然a foreign key constraint fails失败)

insert into emp1(id,name,dept_id)

values

(1,'wang',101)

③删除部门表的财务部

先把子表员工表引用的财务部的记录删除，才能删除父表部门表的记录。

delete from emp1

where dept_id =101;

delete from dept1

where dept_id =101;

9.删除外键约束

10.开发场景

问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否 一定要建外键约束？

不一定

问题2：建和不建外键约束有什么区别？

建立外键:操作受到语法层面的限制

不建立外键：操作不受限制，保证数据引用的完整性，靠程序员自觉或java程序中进行限定

问题3：那么建和不建外键约束和查询有没有关系？

没有

11.阿里开发规范

不使用外键，外键概念在应用层解决。

说明：外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

 

七、检查约束 check

1.作用:

检查某个字段的值的范围符合xx要求

2.关键字

check

3.MySQL5.7不支持，运行不报错，但不起作用。MySQL 8.0支持。Oracle支持

4.示例

create table test10(

id int,

last_name varchar(15),

salary decimal(10,2) check(salary > 2000)

);

①插入演示

insert into test10(id,last_name,salary)

values

(1,'wang',1000);错误

insert into test10(id,last_name,salary)

values

(2,'zhang',3000);正确

八、默认约束 default

1.作用

默认值约束，插入数据时，字段没有显式赋值，就赋值为默认值

2.创建表时增加默认约束

create table test11(

id int,

last_name varchar(15),

salary DECIMAL(10,2) default 2000

);

演示一：

insert into test11(id,last_name,salary)

values

(1,'wang',null), #成功

(1,'wang') #失败，字段和值的数目不匹配

演示二：

insert into test11(id,last_name)

values

(2,'zhang')

3.已有表时增加默认约束

创建默认约束时，检查此字段是否非空，如果非空记得添加 default 默认值 not null；

九、面试

1.为什么建表时，加not null default’’或default 0，为什么？

答：不想出现null值。

①不好比较。参与运算时返回的都是null

②效率不高，影响提高索引效果

面试2：带auto_increment的约束字段值都是从1开始吗？

答：①默认是从1开始，每新增一条记录字段自动加1。

②还可以自定义第一条插入记录的自增字段值，以后插入就会根据这个值自动加1

③添加主键约束时，往往设置字段自增属性

面试3:每个表都可以任意选择存储引擎吗?

外键约束不支持跨引擎使用。

MySQL支持每个表的引擎不同。但是外键约束不支持跨引擎使用，因为保证数据参照的完整性。

如果不同引擎的表之间需要关联外键，那么表之间不能创建外键约束。存储引擎的选择不是完全随意的。

十、练习

练习一

已经存在数据库test04_emp，两张表emp2和dept2

题目：

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

alter table emp2

add primary key(id)

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

alter table dept2

add primary key(id)

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

alter table emp2

add column dept_id int

alter table emp2

add foreign key(dept_id) references dept2(id)

练习二

# 1、创建数据库test01_library

create database if not exists test01_library

# 2、创建表 books，表结构如下：

create table books(

id int,

name varchar(50),

authors varchar(100),

price float,

pubdate year,

note varchar(100),

num int

);

# 3、使用ALTER语句给books按如下要求增加相应的约束

 

Id

alter table books

add primary key(id);

alter table books

modify id int auto_increment;

name

alter table books

modify name varchar(50) not null;

authors

alter table books

modify authors varchar(100) not null;

price

alter table books

modify price float not null;

year

alter table books

modify pubdate year not null;

num

alter table books

modify num int not null;

练习三

#1. 创建数据库test04_company

create database if not exists test04_company

#2. 按照下表给出的表结构在test04_company数据库中创建两个数据表offices和employees

 

create table offices(

officeCode int(10) primary key,

city varchar(50) not null,

address varchar(50),

country varchar(50) not null,

postalCode varchar(15) unique

);

create table employees(

employeeNumber int(11) primary key auto_increment,

lastName varchar(50) not null,

firstName varchar(50) not null,

mobile varchar(25) unique,

officeCode int(10),

jobTitle varchar(50) not null,

birth datetime not null,

note varchar(255),

sex varchar(5),

foreign key(officeCode) references offices(officeCode)

);

#3. 将表employees的mobile字段修改到officeCode字段后面

ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;

#4. 将表employees的birth字段改名为employee_birth

alter table employees

change birth employee_birth datetime not null

#5. 修改sex字段，数据类型为CHAR(1)，非空约束

alter table employees

modify sex char(1) not null

#6. 删除字段note

alter table employees

drop column note

#7. 增加字段名favoriate_activity，数据类型为VARCHAR(100)

alter table employees

add column favoriate_activity varchar(100)

#8. 将表employees名称修改为employees_info

rename table employees to employees_info