MySQL约束和三大范式

精选原创

Anabelle 2024-05-27 10:22:17 博主文章分类：MySQL ©著作权

文章标签 主键 MySQL 第二范式 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者Anabelle的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、约束

1.1.什么是约束？为什么要有约束？

为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
约束是表级别的强制规定
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）

1.2.MySQL有哪些约束？

有以下六种约束：

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

注意：MySQL不支持check约束，但可以使用check约束，而没有任何效果；具体细节可以参阅W3Cschool手册

MySQL 8.0.16 是第一个支持 CHECK 约束的版本。如果使用 MySQL 8.0.15 或更早版本，MySQL 参考手册中明确表示：CHECK 子句会被解析，但被所有存储引擎忽略。如果您想要使用 CHECK 约束，可以尝试使用触发器来实现类似的功能。

1.3.约束分类

根据约束数据列的限制，约束可分为：

单列约束：每个约束只约束一列
多列约束：每个约束可约束多列数据

根据约束的作用范围，约束可分为：

列级约束只能作用在一个列上，跟在列的定义后面
表级约束可以作用在多个列上，不与列一起，而是单独定义

二、NOT NULL 约束

2.1.NOT NULL 约束说明

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

Null类型特征：

所有的类型的值都可以是null，包括int、 float等数据类型
空字符串””不等于null，0也不等于null

2.2.NOT NULL 约束

创建表时使用not null 约束：

CREATE TABLE emp(
    id INT(10) NOT NULL,
    name VARCHAR(20) NOT NULL DEFAULT 'abc',
    sex CHAR NULL
)

增加not null 约束：

ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL;

查看表结构：

MySQL约束和三大范式_主键

上面设置为空约束后，插入数据的时候必须要有值才可以，否则会报错如下：

MySQL约束和三大范式_MySQL_02

2.3.取消NOT NULL 约束

取消 not null 约束：

ALTER TABLE emp MODIFY sex VARCHAR(30) NULL;

查看结构：

MySQL约束和三大范式_主键_03

取消 not null 约束，增加默认值

ALTER TABLE emp MODIFY name VARCHAR(15) DEFAULT 'augus' NULL;

注意：设置默认值后如果插入数据不设置name字段的值，则会使用默认值

二、UNIQUE约束

2.1.UNIQUE约束

同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。MySQL会给唯一约束的列上默认创建一个唯一索引

# 给name列添加唯一约束
CREATE TABLE user(
id INT NOT NULL,
# 给name添加唯一约束
name VARCHAR(25) UNIQUE,
password VARCHAR(16)
)
# 给name和password两列绑定组合唯一约束
CREATE TABLE user(
id INT NOT NULL,
name VARCHAR(25),
password VARCHAR(16),
# 使用表级约束语法，name和password列组合设置唯一约束
CONSTRAINT uk_name_pwd UNIQUE(name,password)
)

上面案例表示用户名和密码组合不能重复

2.2.添加唯一约束

我们除了在创建表的时候添加唯一性约束之外，也可以在创建表完成后，通过alter添加唯一约束：

# 添加唯一约束
ALTER TABLE user ADD UNIQUE(name,password)
# 添加唯一约束，指定约束名称
ALTER TABLE user ADD CONSTRAINT uk_name_pwd UNIQUE(name,password)
# 给单列添加唯一约束
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

2.3.删除约束

ALTER TABLE user DROP INDEX uk_name_pwd

三、PRIMARY KEY 约束

3.1.PRIMARY KEY 约束说明

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。
MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

3.2.添加PRIMARY KEY 约束

列级模式

CREATE TABLE emp1(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20)
)

表级模式

CREATE TABLE emp2(
id INT AUTO_INCREMENT,
name VARCHAR(20),
CONSTRAINT emp2_id_pk PRIMARY KEY(id)
)

组合模式

CREATE TABLE emp3(
id INT NOT NULL,
name VARCHAR(20),
pwd  VARCHAR(20),
CONSTRAINT emp3_id_pk PRIMARY KEY(name,pwd)
)

3.3.主键相关操作

删除主键约束

ALTER TABLE emp3 DROP PRIMARY KEY

添加主键约束

ALTER TABLE emp3 ADD PRIMARY KEY(id,name)

修改主键约束

ALTER TABLE emp3 MODIFY id INT PRIMARY KEY;

四、FOREIGN KEY 约束

4.1.FOREIGN KEY 约束

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
还有一种就是级联删除子表数据。

注意：

外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列
同一个表可以有多个外键约束

4.2.添加FOREIGN KEY 约束

创建外键约束：

主表：

CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);

从表

CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

4.3.创建多列外键组合

创建多列外键组合，必须使用表级约束

主表：

CREATE TABLE classes(
id INT,
name VARCHAR(20),
number INT,
# 创建主键
PRIMARY KEY(name,number)
);

从表：

CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(20),
class_number INT,
FOREIGN KEY(class_name,class_number) REFERENCES classes(name,number)
)

4.4.删除和添加外键约束

删除外键约束：

ALTER TABLE emp DROP FOREIGN KEY emp_dept_id_fk;

增加外键约束

ALTER TABLE emp 
ADD CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);

说明：

FOREIGN KEY: 在表级指定子表中的列
REFERENCES: 标示在父表中的列
ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
ON DELETE SET NULL(级联置空): 子表中相应的列置空

CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(20),
class_number INT,
# 表级别联合外键
FOREIGN KEY(class_name,class_number) REFERENCES classes(name,number) ON DELETE CASCADE
)

五、CHECK约束

MySQL可以使用check约束，但check约束对数据验证没有任何作用,添加数据时，没有任何错误或警告

CREATE TABLE emp4(
id INT AUTO_INCREMENT,
name VARCHAR(20),
age INT CHECK(age>20),
PRIMARY KEY(id)
);

INSERT INTO emp4 VALUES(1,'top',14)
INSERT INTO emp4 VALUES(2,'tom',15)
INSERT INTO emp4 VALUES(3,'augus',23)

六、DEFAULT默认值约束

在插入数据时，如果不写入就使用默认值，如果写入就使用新的值

# 创建表
DROP TABLE IF EXISTS dept2
CREATE TABLE dept2(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25),
location VARCHAR(50) DEFAULT 'shanghai');


INSERT INTO dept2 VALUES (1,'产品部','beijing')
# location字段不插入则会使用默认值
INSERT INTO dept2 (id,name) VALUES (2,'项目部')
INSERT INTO dept2 (name,location) VALUES ('生产部','深圳')

SELECT * FROM dept2

DESC dept2