数据库约束概念
数据库约束是指对数据库表中的数据所施加的规则或条件，用于确保数据的准确性和可靠性。
这些约束可以是基于数据类型、值范围、唯⼀性、⾮空等规则，以确保数据的正确性和相容性
常用约束类型
NOT NULL 非空约束
定义表时某列不允许为NULL时，可以为列添加⾮空约束。
比如创建⼀个学生表，学生名为NULL时，这条记录是不完整的
1
2
3
4
|
CREATE TABLE student(
id BIGINT,
name VARCHAR(20) NOT NULL
);
|
DEFAULT 默认值约束
DEFAULT 约束用于向列中插⼊默认值，如果没有为列设置值，那么会将默认值设置到该列
重构学生表，新增年龄列
1
2
3
4
5
|
CREATE TABLE student(
id BIGINT,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 18
);
|
UNIQUE 唯一约束
指定了唯⼀约束的列，该列的值在所有记录中不能重复，比如⼀个人的身份证号，学生的学号等
重构学生表，新增学号列
1
2
3
4
5
6
|
CREATE TABLE student(
id BIGINT,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 18,
sno VARCHAR(20) UNIQUE
);
|
PRIMARY KEY 主键约束
主键约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值，且不能包含 NULL 值。
每个表只能有一个主键，可以由单个列或多个列组成。
通常为每张表都指定一个主键，主键列建议使用BIGINT类型
重构学生表，为ID列添加非空和唯一约束
1
2
3
4
5
6
|
CREATE TABLE student(
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 18,
sno VARCHAR(20) UNIQUE
);
|
PRIMARY KEY 等价于 NOT NULL + UNIQUE
AUTO_INCREMENT 自动增长,插入数据时不设置主键列的值(设置为NULL),不指定主键，主键的值会自动生成
如果某条记录写入失败，新生成的主键值将会作废
主键值可以不连续
主键或唯⼀键冲突时的更新操作，插入否则更新
1
2
3
4
5
6
|
INSERT INTO 表名 (字段1, 字段2, ...)
VALUES (值1, 值2, ...)
ON DUPLICATE KEY UPDATE
字段1 = 新值1,
字段2 = 新值2,
...;
|
如果存在冲突则替换，不存在冲突则插入
1
2
|
REPLACE INTO 表名 (字段1, 字段2, ...)
VALUES (值1, 值2, ...);
|
表中不能有多个主键
复合主键：由多个列共同组成的主键，主键是否冲突以多个列的组成进行判定
1
2
3
4
5
6
7
|
CREATE TABLE student(
id BIGINT ,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 18,
sno VARCHAR(20) UNIQUE,
PRIMARY KEY (id,sno)
);
|
FOREIGN KEY 外键约束
外键（FOREIGN KEY）是用于在两个表之间建立关联关系的约束，它确保从表（子表）中的数据在主表（父表）中存在对应记录，从而维护数据库的引用完整性。
外键约束定义在从表中，它引用主表中的主键或唯一键列。
例如：学生表的班级编号关联班级表的id编号
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
-- 班级表
CREATE TABLE class(
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL
);
-- 学生表
CREATE TABLE student(
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 18,
sno VARCHAR(20) UNIQUE,
class_id BIGINT,
FOREIGN KEY (class_id) REFERENCES class(id)
);
|
删除主表时要先删除从表
CHECK 检查约束
CHECK 约束用于限制表中字段的取值范围，确保字段值满足指定的条件，从而维护数据的完整性
1
2
3
4
5
6
7
8
9
|
CREATE TABLE 表名 (
字段1 数据类型,
字段2 数据类型,
...,
-- 字段级 CHECK 约束
CONSTRAINT 约束名 CHECK (条件),
-- 表级 CHECK 约束（可涉及多个字段）
CONSTRAINT 约束名 CHECK (多字段条件)
);
|
示例：
1
2
3
4
5
6
7
8
9
|
CREATE TABLE student(
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 18,
sno VARCHAR(20) UNIQUE,
class_id BIGINT,
FOREIGN KEY (class_id) REFERENCES class(id),
CHECK (age >= 18 AND age <= 60)
);
|
已有表约束的添加和删除
添加约束
- 添加主键约束（PRIMARY KEY）
1
2
3
4
5
6
7
|
-- 语法
ALTER TABLE 表名
ADD PRIMARY KEY (字段名);
-- 示例：为users表的id字段添加主键
ALTER TABLE users
ADD PRIMARY KEY (id);
|
- 添加外键约束（FOREIGN KEY）
1
2
3
4
5
6
7
8
9
10
11
12
13
|
-- 语法
ALTER TABLE 从表名
ADD CONSTRAINT 外键约束名
FOREIGN KEY (从表字段)
REFERENCES 主表名(主表字段)
[ON DELETE 操作] [ON UPDATE 操作];
-- 示例：为employees表添加外键关联departments表
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE SET NULL ON UPDATE CASCADE;
|
- 添加唯一约束（UNIQUE）
1
2
3
4
5
6
7
|
-- 语法
ALTER TABLE 表名
ADD UNIQUE [约束名] (字段名);
-- 示例：为users表的email字段添加唯一约束
ALTER TABLE users
ADD UNIQUE uk_email (email);
|
- 添加非空约束（NOT NULL）
1
2
3
4
5
6
7
|
-- 语法（通过修改字段属性实现）
ALTER TABLE 表名
MODIFY 字段名 数据类型 NOT NULL;
-- 示例：设置users表的name字段为非空
ALTER TABLE users
MODIFY name VARCHAR(50) NOT NULL;
|
- 添加检查约束（CHECK）
1
2
3
4
5
6
7
|
-- 语法
ALTER TABLE 表名
ADD CONSTRAINT 约束名 CHECK (条件);
-- 示例：限制products表的price字段必须大于0
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);
|
- 添加默认值约束（DEFAULT）
1
2
3
4
5
6
7
|
-- 语法（通过修改字段属性实现）
ALTER TABLE 表名
MODIFY 字段名 数据类型 DEFAULT 默认值;
-- 示例：为orders表的status字段设置默认值
ALTER TABLE orders
MODIFY status VARCHAR(20) DEFAULT 'pending';
|
删除约束
- 删除主键约束
1
2
3
4
5
6
7
|
-- 语法
ALTER TABLE 表名
DROP PRIMARY KEY;
-- 示例
ALTER TABLE users
DROP PRIMARY KEY;
|
- 删除外键约束
1
2
3
4
5
6
7
8
9
10
|
-- 语法（需要先知道外键约束名）
ALTER TABLE 表名
DROP FOREIGN KEY 外键约束名;
-- 查看外键约束名
SHOW CREATE TABLE 表名;
-- 示例
ALTER TABLE employees
DROP FOREIGN KEY fk_emp_dept;
|
- 删除唯一约束
1
2
3
4
5
6
7
|
-- 语法（需要知道约束名或索引名）
ALTER TABLE 表名
DROP INDEX 约束名;
-- 示例
ALTER TABLE users
DROP INDEX uk_email;
|
- 删除检查约束
1
2
3
4
5
6
7
|
-- 语法
ALTER TABLE 表名
DROP CONSTRAINT 约束名;
-- 示例
ALTER TABLE products
DROP CONSTRAINT chk_price;
|
- 删除非空约束或默认值约束
1
2
3
4
5
6
7
8
9
10
11
|
-- 语法（通过修改字段属性实现）
ALTER TABLE 表名
MODIFY 字段名 数据类型 [NULL] [DEFAULT NULL];
-- 示例：取消name字段的非空约束
ALTER TABLE users
MODIFY name VARCHAR(50) NULL;
-- 示例：取消status字段的默认值
ALTER TABLE orders
MODIFY status VARCHAR(20) DEFAULT NULL;
|