约束

为什么需要约束？

为了保证数据的完整性，对数据操作进行额外的条件限制

  • 实体完整性约束(Entity Integrity)：同一张表中，不能存储两条完全相同的记录
  • 域完整性(Domain Integrity)：年龄范围0-120
  • 引用完整性(Referential Integrity)：员工所在部门，必须要在部门表中找到相应的部门
  • 用户自定义完整性(User-defined Integrity)：用户名唯一、密码不能为空等

什么是约束？

在创建表时规定或创建表后通过ALTER TABLE语句规定约束。

约束的分类

  • 根据约束数据列的限制，约束可分为：
    • 单列约束：每个约束只约束一列
    • 多列约束：每个约束可约束多列数据
  • 根据约束的作用范围，约束可分为：
    • 列级约束：只能作用在一个列上，跟在列的定义后面
    • 表级约束：可以作用在多个列上，不与列一起，而是单独定义

image-20221021113759782

image-20221021113847661

查看表的约束情况：

#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';

非空约束

0不等于null，空字符串不等于null

只能作用到列，不能组合使用

创建表的时候添加约束

CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);

创建表后添加约束

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

删除约束方式一

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

删除约束方式二

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

唯一约束

image-20221021120152379

create table 表名称(
字段名 数据类型,
字段名 数据类型 unique, # 列级约束
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名) # 表级约束
);

image-20221021122710211

复合的唯一性约束

CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);

删除唯一约束

  • 添加唯一性约束的列上也会自动创建唯一索引。
  • 删除唯一约束只能通过删除唯一索引的方式删除。
  • 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
  • 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和() 中排在第一个的列名相同。也可以自定义唯一性约束名。

查看表的约束情况

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';

注意：可以通过show index from 表名，来查看索引。

删除索引，即删除唯一约束

ALTER TABLE USER
DROP INDEX uk_name_pwd;

主键约束

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

一张表中只能有一个主键约束，主键约束要求是非空约束和唯一约束的组合。

需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的 值，就有可能会破坏数据的完整性。

在创建表时指定主键约束

CREATE TABLE test3(
id INT PRIMARY KEY, # 列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

CREATE TABLE test3(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#CONSTRAINT pk_test3_id PRIMARY KEY(id)
PRIMARY KEY(id)
);

注意：这里CONSTRAINT pk_test3_id PRIMARY KEY(id)没有必要起名字，起名字也不报错，但是也不起效果，主键名字还是PRIMARY。

外键约束

FOREIGN KEY

先创建主表，再创建从表

先删从表（或先删除外键约束），再删除主表

从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类 型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。

删除外键约束后，必须 手动 删除对应的索引。

创建外键约束

建表时创建外键约束

格式：

create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

举例：

create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);

建表后添加外键约束

格式：

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];

举例：

ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

约束等级

image-20221021183544428

删除外键约束

删除外键约束

ALTER TABLE empl
DROP FOREIGN KEY 外键名

再手动删除外键约束对应的索引

ALTER TABLE empl
DROP INDEX 外键约束名

开发中不推荐使用外键约束

image-20221021185115030

CHECK约束

作用：检查某个字段的值是否符合要，一般是指值的范围。

注意：MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用，添加数据时，没有任何错误或警告，但是MySQL 8.0中可以使用check约束了。

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

CHECK(height>=0 AND height<3)

DEFAULT约束

作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默 认值。

添加默认值

建表时添加默认值

create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);

默认值约束一般不在唯一键和主键列上加。

建表后添加默认值

alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被
删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默
认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

alter table employee modify tel char(11) default '' not null;#给tel字段增加默认值约束，并保留非空约束

删除默认值约束

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除
alter table employee modify tel char(11) not null;#删除tel字段默认值约束，保留非空约束

面试

image-20221021191642815

Q.E.D.

热爱生活，热爱程序