java学习 --- MySQL的约束

目录

一、为什么要约束

二、约束是什么

三、约束的分类

四、查看表中的约束

五、非空约束

六、唯一约束

七、主键约束

八、自增列

九、外键约束（不建议使用）

十、检查约束

十一、默认约束

---

一、为什么要约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：
实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍

二、约束是什么

约束是表级的强制规定。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

三、约束的分类

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

根据约束起的作用，约束可分为：
NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)约束
FOREIGN KEY 外键约束
CHECK 检查约束
DEFAULT 默认值约束

四、查看表中的约束

SELECT * FROM information_schema.table_constraints WHERE `table_name` = 'employess';

五、非空约束

限定某个字段/某列的值不允许为空

#创建数据库
CREATE DATABASE db01;
#使用数据库
USE db01;
#创建数据库表
CREATE TABLE test01(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);
#查看表的结构
DESC test01;
#正确添加数据
INSERT INTO test01(id,last_name,email,salary)VALUES(1,'小明','6379901@qq.com',5600);
INSERT INTO test01(id,last_name,email,salary)VALUES(1,'小米',NULL,5600);
#错误添加数据 Column 'last_name' cannot be null
INSERT INTO test01(id,last_name,email,salary)VALUES(1,NULL,'6379901@qq.com',5600);
#错误的修改
UPDATE test01 SET last_name = NULL WHERE id = 1;
#修改表的字段约束,当我们表中的当前字段有空值，不能修改  Invalid use of NULL value
ALTER TABLE test01 MODIFY email VARCHAR(25) NOT NULL;
#修改或者删除表的字段约束
ALTER TABLE test01 MODIFY email VARCHAR(25)  NULL;

六、唯一约束

用来限制某个字段/某列的值不能重复。

#创建表
create table test02(
id int unique,  #列级唯一约束
last_name varchar(15),
email VARCHAR(25) UNIQUE,
salary DECIMAL(10,2),
#这里使用的取别名
constraint uk_test02_email UNIQUE(email) #表级唯一约束
);
#查看表结构
desc test02;
#查看约束
SELECT * FROM information_schema.table_constraints WHERE `table_name` = 'test02';
#正确添加数据
insert into test02 values(1,'小妹','456789@qq.com',5600);
#错误添加数据 Duplicate entry '1' for key 'test02.id'
INSERT INTO test02 VALUES(1,'小丽','456789@qq.com',5600);
#当字段为唯一约束但没有非空约束时可以添加null且可以多个
INSERT INTO test02 VALUES(2,'小妹',null,5600);
INSERT INTO test02 VALUES(3,'小妹',NULL,5600);
#修改表的字段约束方式一
ALTER TABLE test02 MODIFY last_name VARCHAR(15) UNIQUE;
#修改表的字段约束方式二
alter table test02 add UNIQUE(salary);
#删除唯一约束就是删除唯一索引名
alter table test02 drop index last_name;

#复合唯一约束
create table test03(
id int,
`name` varchar(25),
`password` varchar(35),
#表级约束
constraint uk_user_name_pwd UNIQUE(`name`,`password`)
);
#添加数据当两个字段中其中一个不一样就能成功
insert into test03 values(1,'小王',123456);
INSERT INTO test03 VALUES(1,'小王1',123456);

七、主键约束

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

#一张表只能有一个主键约束 Multiple primary key defined
#错误创建
CREATE TABLE test04(
id INT PRIMARY KEY, #列级主键约束
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);

CREATE TABLE test04(
id INT PRIMARY KEY, #列级主键约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
#查看表结构
DESC test04;
#正确添加数据
INSERT INTO test04 VALUES(1,'小王',7800,'456789@qq.com');
#错误添加数据 Duplicate entry '1' for key 'test04.PRIMARY'
INSERT INTO test04 VALUES(1,'小王1',7800,'456789@qq.com');
#Column 'id' cannot be null
INSERT INTO test04 VALUES(NULL,'小王1',7800,'456789@qq.com');
#删除主键约束
ALTER TABLE test04 DROP PRIMARY KEY;
#添加表的字段约束
ALTER TABLE test04 ADD PRIMARY KEY(id);

#主键约束的复合使用
CREATE TABLE test05(
id INT,
`name` VARCHAR(25),
`password` VARCHAR(35),
PRIMARY KEY(`name`,`password`)
);
#添加数据
INSERT INTO test05 VALUES(1,'老王','123456');
INSERT INTO test05 VALUES(2,'老王2','123456');
#错误添加数据 Duplicate entry '小王-123456' for key 'test05.PRIMARY'
INSERT INTO test05 VALUES(3,'小王','123456');
INSERT INTO test05 VALUES(4,'小王','123456');
#Column 'password' cannot be null
INSERT INTO test05 VALUES(4,'小王',NULL);

八、自增列

#在主键上创建自增列
CREATE TABLE test06(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(25)
);
#添加数据
INSERT INTO test06(last_name) VALUES('tom');
#当添加数据时使用0和null时会自动自增
INSERT INTO test06 VALUES(0,'tom');
INSERT INTO test06 VALUES(NULL,'tom');

九、外键约束（不建议使用）

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

#创建主表
CREATE TABLE dept1(
 dept_id INT PRIMARY KEY ,
 dept_name VARCHAR(15)
);
#创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(25),
dept_id INT,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (dept_id) REFERENCES dept1(dept_id)
);
#在添加数据时，如果主表没有数据，从表添加也会失败
INSERT INTO dept1 VALUES(1,'开发部');
INSERT INTO emp1 VALUES(NULL,'小王',1);

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

十、检查约束

检查某个字段的值是否符号xx要求，一般指的是值的范围

#创建表
CREATE TABLE test07(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15),
salary DECIMAL(10,5) CHECK(salary > 3000) #定义check约束
);
#正确添加数据
INSERT INTO test07 VALUES(NULL,'小王',4000);
#错误添加数据
INSERT INTO test07 VALUES(NULL,'小王',2000);

十一、默认约束

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

CREATE TABLE test08(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15),
salary DECIMAL(10,4) DEFAULT(3000) #默认3000
);
#添加数据
INSERT INTO test08(last_name) VALUES('jack');