目录
一、概述
1、概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
2、目的:保证数据库中数据的正确、有效性和完整性。
3、分类:
约束类型 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束 (8.0.1.6版本之后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
二、使用约束
案例
根据需求,完成表结构的创建
字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
id | ID唯一标识 | int | 主键,并且自动增长 | PRIMARY KEY, AUTO_INCREMENT |
name | 姓名 | varchar(10) | 非空且唯一 | NOT NULL, UNIQUE |
age | 年龄 | int | 大于0并且小于120 | CHECK |
status | 状态 | char(1) | 如果没有指定该值,默认为1 | DEFAULT |
gender | 性别 | char(1) | 无 | / |
-- --------------- 约束 ---------------------
USE my_database;
# 创建表
CREATE TABLE user(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID主键',
name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
age INT CHECK ( age > 0 AND age <= 120) COMMENT '年龄',
status CHAR(1) DEFAULT '1' COMMENT '状态',
gender CHAR(1) COMMENT '性别'
) COMMENT '用户信息表';
# 插入数据
INSERT INTO user(name, age, status, gender)
VALUES ('Tom', 18, '1', '男'),
('Jack', 19, '1', '男');
# 查询
SELECT * FROM user;
# 测试插入其他数据
# 1.测试非空约束
# Column 'name' cannot be null
INSERT INTO user(name, age, status, gender)
VALUES (null, 1, '1', '男');
# 2.测试唯一约束
# Duplicate entry 'Tom' for key 'user.name'
INSERT INTO user(name, age, status, gender)
VALUES ('Tom', 1, '1', '男');
# 3.测试检查约束
# Check constraint 'user_chk_1' is violate
INSERT INTO user(name, age, status, gender)
VALUES ('Jimmy', 121, '1', '男');
# 4.测试默认约束
# status默认为1
INSERT INTO user(name, age, gender)
VALUES ('Jimmy', 39, '男');
SELECT * FROM user;
三、外键约束
概念:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
掌握:
- 添加外键
- 删除外键
- 外键约束的更新/删除行为
比如
语法:
关键字:FOREIGN KEY
- 添加外键
- 创建表时添加外键约束语法:
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
)[COMMENT 注释];
- 修改表时添加外键约束语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);
- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
- 外键约束的删除/更新行为
行为 | 说明 |
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与RESTRICT一致) |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有,则设置子表中的该外键对应的值为null。(这就要求改外键允许取值为null) |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)。 |
语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FORREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE 行为 ON DELETE 行为;
外键约束示例:
-- ------------ 约束 外键约束-----------------
USE my_database;
-- 创建新表之前,先删除之前的emp/employee表,防止混淆
DROP TABLE IF EXISTS emp, employee, dept;
-- 准备数据
# 创建部门表
CREATE TABLE dept
(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
name VARCHAR(10) NOT NULL COMMENT '部门名称'
) COMMENT '部门信息表';
# 插入部门数据
INSERT INTO dept (name)
VALUES ('研发部'),
('市场部'),
('财务部'),
('销售部'),
('总经办');
SELECT *
FROM dept;
# 创建员工信息表
CREATE TABLE emp
(
id INT AUTO_INCREMENT COMMENT '员工ID' PRIMARY KEY,
name VARCHAR(40) NOT NULL COMMENT '姓名',
age INT CHECK ( age > 0 AND age < 120 ) COMMENT '年龄',
job VARCHAR(20) COMMENT '职位',
salary INT COMMENT '薪资',
entry_date DATE COMMENT '入职时间',
manager_id INT COMMENT '直属领导ID',
dept_id INT COMMENT '部门ID'
);
# 插入员工数据
# 由于员工id是自增的,所以无需插入
INSERT INTO emp(name, age, job, salary, entry_date, manager_id, dept_id)
VALUES ('金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
('张无忌', 30, '项目经理', 12000, '2005-12-05', 1, 2),
('杨逍', 35, '开发', 10000, '2001-11-03', 2, 1),
('韦一笑', 40, '开发', 8000, '2002-03-22', 2, 1),
('常遇春', 43, '开发', 7000, '2003-04-28', 2, 1),
('小昭', 24, '会计', 6000, '2005-10-11', 1, 3);
SELECT *
FROM emp;
# 添加外键
ALTER TABLE emp
ADD CONSTRAINT fk_worker_dept_id FOREIGN KEY (dept_id) REFERENCES dept (id);
# 删除外键
ALTER TABLE emp
DROP CONSTRAINT fk_worker_dept_id;
# 删除/更新行为
ALTER TABLE emp
ADD CONSTRAINT fk_worker_dept_id FOREIGN KEY (dept_id) REFERENCES dept (id) ON UPDATE CASCADE ON DELETE CASCADE;
SELECT * FROM dept;
SELECT * FROM emp;
# 如果我们在表dept中将研发部的ID修改为6
UPDATE dept SET id = 6 WHERE name = '研发部';
SELECT * FROM dept;
# 再来查看表emp,由于外键约束行为UPDATE为CASCADE(级联),可以看到表emp中的dept_id也随之更新
SELECT * FROM emp;
四、约束-小结
1、非空约束:NOT NULL
2、唯一约束:UNIQUE
3、主键约束:PRIMARY KEY (自增:AUTO_INCREMENT #仅限MySQL)
4、默认约束:DEFAULT
5、检查约束:CHECK
6、外键约束:FOREIGN KEY