约束

mysql
, ,

约束(constraint)

概述

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

  • 实体完整性：同一个表中不能存在两个完全相同无法区分的数据
  • 域完整性：例如年龄范围(0-120)
  • 引用完整性：例如员工所在部门，部门表中要有该部门
  • 用户自定义完整性：例如用户名不能重复，密码不能为空等

分类

1. 按约束字段的个数

单列约束 & 多列约束

2. 按约束的作用范围

列级约束：声明在对应的字段后面

表级约束：声明在所有字段的后面

3. 按约束的作用

NOT NULL (非空约束)

UNIQUE (唯一性约束)

PRIMARY KEY (主键约束)

FOREIGN KEY (外键约束)

CHECK (检查约束)

DEFAULT (默认值约束)

使用语法

添加 / 删除约束

CREATE TABLE时添加

ALTER TABLE时添加、删除

查看表中约束

1
2
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'table_name';

具体用法

非空约束(NOT NULL)

描述

限制某列的值不能为NULL

添加与删除

1
2
3
4
5
6
7
8
9
10
11
12
#建表时
CREATE TABLE t_name(
	col1_name INT,
    col2_name INT NOT NULL,
    col3_name VARCHAR(15) NOT NULL
);
#ALTER TABLE
ALTER TABLE t_name
MODIFY col_name INT NOT NULL;
#删除
ALTER TABLE t_name
MODIFY col_name INT NULL;

唯一性约束(UNIQUE)

描述

限制某列的值不能重复(但可出现多个空值)

添加与删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#列级约束
CREATE TABLE t_name(
	col1_name INT UNIQUE,
    col2_name INT,
    col3_name VARCHAR(15) UNIQUE 
);

#表级约束
CREATE TABLE t_name(
	col1_name INT,
    col2_name INT,
    col3_name VARCHAR(15),  
    CONSTRAINT uk_tableName_colName UNIQUE(colName);#uk:UNIQUE, 遵循命名规律
);

#建表后
ALTER TABLE t_name
ADD CONSTRAINT uk_tableName_colName UNIQUE(colName);

ALTER TABLE t_name
MODIFY col_name INT UNIQUE;

复合的唯一性约束

1
2
3
4
5
6
7
#eg
CREATE TABLE user(
	id INT,
    name VARCHAR(15),
    password VARCHAR(25),
    CONSTRAINT uk_user_name_pwd UNIQUE(name, password)
);

name和password不能同时相同，但其中一个存在相同值是可以的

主键约束(PRIMARY KEY)

描述

非空且唯一，用于唯一标识表中的一条记录

一个表中只能有一个

添加与删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#列级约束
CREATE TABLE t_name(
	id INT PRIMARY KEY,
    last_name VARCHAR(15),
    salary DECIMAL(10, 2)
);
#表级约束
CREATE TABLE t_name(
	id INT,
    last_name VARCHAR(15),
    salary DECIMAL(10, 2),
    CONSTRAINT pk_tName_id PRIMARY KEY(id) #没必要取名，总是PRIMARY
);
#建表后
ALTER TABLE t_name
ADD PRIMARY KEY (col_name);
#删除，实际上不会使用
ALTER TABLE t_name
DROP PRIMARY KEY;

复合主键约束

1
2
3
4
5
6
CREATE TABLE user(
	id INT,
    name VARCHAR(15),
    password VARCHAR(25),
    PRIMARY KEY(name, password)
);

name和password不能同时相同，且任何一个都不能为NULL

自增列(AUTO_INCREMENT)

描述

某个字段值自增

一个表中只能有一个

约束的列必须是键列(主键，唯一键)

数据类型必须整型

添加

1
2
3
4
5
6
7
8
9
10
CREATE TABLE t_name(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(15)
);

ALTER TABLE t_name
MODIFY id INT AUTO_INCREMENT;
#删除
ALTER TABLE t_name
MODIFY id INT;

注意

  • 给自增列添加0或NULL，会自动递增添加数值(0，NULL无效)

  • 一般不给自增的列赋值

外键约束(FORIGN KEY)

一般不会使用

检查约束(CHECK)

描述

检查某个字段值是否满足某要求

MySQL5.7不支持

添加

1
2
3
4
5
6
7
8
CREATE TABLE t_name(
	id INT PRIMARY KEY,
    salary DECIMAL(10,2) CHECK(salary > 2000)
	sex CHAR(2) CHECK(sex IN('M','F')
);
#删除
ALTER TABLE t_name
MODIFY salary DECIMAL(10,2);

默认值约束(DEFAULT)

描述

给字段添加默认值，若添加时没有显式赋值，则赋值为默认值

添加与删除

1
2
3
4
5
6
7
CREATE TABLE t_name(
	id INT PRIMARY KEY,
    salary DECIMAL(10,2) DEFAULT 2500
);
#删除
ALTER TABLE t_name
MODIFY salary DECIMAL(10,2);