目录
外键约束 FOREIGN KEY ... REFERENCES(了解)
约束总结
为什么需要约束? 为了保证数据的完整性!什么叫约束?对表中字段的限制。
数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。为了保证数据的完整性,SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑:
实体完整性(Entity Integrity) :例如,同一个表中,不能存在两条完全相同无法区分的记录
域完整性(Domain Integrity) :例如:年龄范围0-120,性别范围“男/女”
引用完整性(Referential Integrity) :例如:员工所在部门,在部门表中要能找到这个部门
用户自定义完整性(User-defined Integrity) :例如:用户名唯一、密码不能为空等,本部门经理的工资不得高于本部门职工的平均工资的5倍。
- 如何查看表中的约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名';
- 约束的分类:
一:按约束的字段的个数
单列约束 vs 多列约束
二:约束的作用范围
列级约束:将此约束声明在对应字段的后面
表级约束:在表中所有字段都声明完,在所有字段的后面声明的约束
三:约束的作用(或功能)
① not null (非空约束)
② unique (唯一性约束)
③ primary key (主键约束)
④ foreign key (外键约束)
⑤ check (检查约束)
⑥ default (默认值约束)
- 如何添加/删除约束?
CREATE TABLE 时添加约束
ALTER TABLE 时增加约束、删除约束
非空约束 NOT NULL (常用)
默认,所有的类型的值都可以是NULL,包括INT、FLOAT等数据类型
- CREATE TABLE时添加约束
CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(15),
salary DECIMAL(10,2)
);
INSERT INTO test1(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',3400);
#错误:Column 'id' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(NULL,'Jerry','jerry@126.com',3400);
#Field 'last_name' doesn't have a default value
INSERT INTO test1(id,email)
VALUES(2,'abc@126.com');
- 在ALTER TABLE时添加、删除约束
SELECT * FROM test1;
DESC test1;
#Invalid use of NULL value 如果当前列表存在空值,无法修改
ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;
# 删除NOT NULL 约束
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;
唯一性约束 UNIQUE (常用)
- 在CREATE TABLE时添加约束
在CREATE TABLE时添加约束
CREATE TABLE test2(
id INT UNIQUE, #列级约束
last_name VARCHAR(15) ,
email VARCHAR(25),
salary DECIMAL(10,2),
#表级约束
CONSTRAINT uk_test2_email UNIQUE(email)
#UNIQUE(email) 不起名,默认为列名
);
- 在ALTER TABLE时添加约束
在ALTER TABLE时添加约束
DESC test2;
UPDATE test2
SET salary = 5000
WHERE id = 3;
#方式1:ADD CONSTRAINT
ALTER TABLE test2 #修改时当前列不能存在相同的值
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
#ADD UNIQUE(salary); 不命名默认与列明相同
#方式2: MODIFY
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;
- 复合的唯一性约束
复合的唯一性约束
CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
#表级约束
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);
#只要复合约束中有一个字段不同,就可以添加
INSERT INTO USER
VALUES(1,'Tom','abc');
#可以成功的:
INSERT INTO USER
VALUES(1,'Tom1','abc');
-
删除唯一性约束 DROP INDEX -- 通过唯一索引名
删除唯一性约束
-- 添加唯一性约束的列上也会自动创建唯一索引。
-- 删除唯一约束只能通过删除唯一索引的方式删除。
-- 删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样。
-- 如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同;如果是组合列,那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
#查看表中约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test2';
如何删除唯一性索引 -- 通过唯一索引名
ALTER TABLE test2
DROP INDEX last_name;
ALTER TABLE test2
DROP INDEX uk_test2_sal;
主键约束 PRIMARY KEY(常用)
主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值。
MySQL的主键名总是PRIMARY,就算自己命名了主键约束名也没用。
如果没有主键约束,系统会自动选择最优的字段添加主键约束,每一个表中一定有一个主键约束。
如果删除主键约束了,主键约束对应的索引就自动删除了。 需要注意的一点是,不要修改主键字段的值。因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性。
- 添加主键约束
一个表中最多只能有一个主键约束。
#错误:Multiple primary key defined
CREATE TABLE test3(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);
# 主键约束特征:非空且唯一,用于唯一的标识表中的一条记录。
CREATE TABLE test4(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
#MySQL的主键名总是PRIMARY,就算自己命名了主键约束名也没用。
CREATE TABLE test5(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表级约束
PRIMARY KEY(id) #没有必要起名字。
);
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test5';
INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');
#错误:Duplicate entry '1' for key 'test4.PRIMARY'
INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');
#错误:Column 'id' cannot be null
INSERT INTO test4(id,last_name,salary,email)
VALUES(NULL,'Tom',4500,'tom@126.com');
- 复合主键约束
#如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。
CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY (NAME,PASSWORD)
);
- 在ALTER TABLE时添加约束
在ALTER TABLE时添加约束
ALTER TABLE test6
ADD PRIMARY KEY (id);
自增列 AUTO_INCREMENT
刷新主键自增
语法:id从1000开始自增:
ALTER TABLE 表名 AUTO_INCREMENT = 1000
在CREATE TABLE时添加
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
#开发中,一旦主键作用的字段上声明有AUTO_INCREMENT,则我们在添加数据时,就不要给主键
#对应的字段去赋值了。
INSERT INTO test7(last_name)
VALUES('Tom');
在ALTER TABLE 时添加、删除约束
CREATE TABLE test8(
id INT PRIMARY KEY ,
last_name VARCHAR(15)
);
#删除
ALTER TABLE test8
MODIFY id INT ;
- MySQL 8.0新特性—自增变量的持久化
外键约束 FOREIGN KEY ... REFERENCES(了解)
限定某个表的某个字段的引用完整性。
引用完整性(Referential Integrity) :例如:员工所在部门,在部门表中要能找到这个部门
约束关系是针对双方的
添加了外键约束后,主表的修改和删除数据受约束
添加了外键约束后,从表的添加和修改数据受约束
在从表上建立外键,要求主表必须存在
删除主表时,要求从表从表先删除,或将从表中外键引用该主表的关系先删除
- 特点
(1)从表的外键列,必须引用/参考主表的主键或唯一约束的列。因为被依赖/被参考的值必须是唯一的。
(2)在创建外键约束时,如果不给外键约束命名,默认名不是列名,而是自动产生一个外键名(例如student_ibfk_1;),也可以指定外键约束名。
(3)创建(CREATE)表时指定外键约束时,先创建主表,再创建从表。
(4)删表时,先删从表(或先删除外键约束),再删除主表。
(5)当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据。
(6)在“从表”中指定外键约束,并且一个表可以建立多个外键约束。
(7)从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致。如果类型不一样,创建子表时,就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。例如:都是表示部门编号,都是int类型。
(8)当创建外键约束时,系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。(根据外键查询效率很高)
(9)删除外键约束后,必须 手动 删除对应的索引。
主表和从表;父表和子表
#①先创建主表
CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);
#②再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT kf_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
);
#上述操作报错,因为主表中的dept_id上没有主键约束或唯一性约束。
#③ 添加 主键约束
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);
DESC dept1;
#④ 再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
);
DESC emp1;
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'emp1';
在ALTER TABLE时添加外键约束
CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
删除外键约束
#删除外键约束
ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;
#再手动的删除外键约束对应的普通索引
SHOW INDEX FROM emp1;
ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;
约束等级
如果没有指定等级,就相当于Restrict方式。
-
Cascade(级联) 方式 :在父表上update/delete记录时,同步update/delete掉子表的匹配记录
-
Set null方式 :在父表上update/delete记录时,将子表上匹配记录的列设为null,但是要注意子表的外键列不能为not null
-
No action(行动) 方式 :如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
-
Restrict(限制) 方式 :同no action, 都是立即检查外键约束
-
Set default方式 (在可视化工具SQLyog中可能显示空白):父表有变更时,子表将外键列设置成一个默认的值,但Innodb不能识别
对于外键约束,最好是采用: ON UPDATE CASCADE(级联) ON DELETE RESTRICT(限制) 的方式。
# on update cascade on delete set null
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) ON UPDATE CASCADE ON DELETE SET NULL
#把修改操作设置为级联修改等级,把删除操作设置为set null等级
);
INSERT INTO dept VALUES(1001,'教学部');
INSERT INTO dept VALUES(1002, '财务部');
INSERT INTO dept VALUES(1003, '咨询部');
INSERT INTO emp VALUES(1,'张三',1001); #在添加这条记录时,要求部门表有1001部门
INSERT INTO emp VALUES(2,'李四',1001);
INSERT INTO emp VALUES(3,'王五',1002);
UPDATE dept
SET did = 1004
WHERE did = 1002;
DELETE FROM dept
WHERE did = 1004;
SELECT * FROM dept;
SELECT * FROM emp; 部门ID 1002改为1004 删除1004改为空
#结论:对于外键约束,最好是采用: `ON UPDATE CASCADE ON DELETE RESTRICT` 的方式。
何时使用外键约束?
建议:Mysql给我们提供了这样的功能,用于体现引用的完整性,但在实际开发中,在Java应用层中解决这个问题,不使用外键约束。
![](https://i-blog.csdnimg.cn/blog_migrate/3f5be93a15e48f5ee5c275d7dbeeec6e.png)
阿里开发规范不得使用外键与级联,一切外键概念必须在应用层解决。说明:(概念解释)学生表中的 student_id 是主键,那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id ,同时触发成绩表中的 student_id 更新,即为级联更新。外键与级联更新适用于 单机低并发 ,不适合 分布式 、 高并发集群 ;级联更新是强阻塞,存在数据库 更新风暴 的风险;外键影响数据库的 插入速度 。
CHECK 约束 (5.7不支持)
作用:检查某个字段的值是否符号xx要求,一般指的是值的范围
MySQL 5.7 不支持
MySQL5.7 可以使用check约束,但check约束对数据验证没有任何作用。添加数据时,没有任何错误或警告
MySQL 8.0中可以使用check约束
check 约束
# MySQL5.7 不支持CHECK约束,MySQL8.0支持CHECK约束。
CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary > 2000)
);
INSERT INTO test10
VALUES(1,'Tom',2500);
#添加失败
INSERT INTO test10
VALUES(2,'Tom1',1500);
SELECT * FROM test10;
DEFAULT 约束 (默认值)
给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值。
#DEFAULT约束
#在CREATE TABLE添加约束
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);
DESC test11;
INSERT INTO test11(id,last_name,salary)
VALUES(1,'Tom',3000);
INSERT INTO test11(id,last_name)
VALUES(2,'Tom1'); #默认2000
- 在ALTER TABLE添加、删除约束
#在ALTER TABLE添加约束
CREATE TABLE test12(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2)
);
ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 2500;
#在ALTER TABLE删除约束
ALTER TABLE test12
MODIFY salary DECIMAL(8,2);
SHOW CREATE TABLE test12;