初识数据库约束

最近实现了一个需求:后台解析上传的Excel数据并将其保存到数据库。由于数据比较敏感,且部分数据是由人工处理的,因此必须确保数据的准确性和唯一性,即在某几个字段相同的情况下其的数据必须唯一 。之前只是简单了解了数据库的约束,这里有必要深入一下。

<!--more-->

实际上,在平常的使用中已经不知不觉接触到立刻

参考资料:

1. 约束简介

先来看看百科上给出的定义

数据完整性约束指的是为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。

我的理解就是:为了保证数据的正确性,在设计表结构的时候,预先指定一些规则,这些规则将在数据发生改变时被触发,从而检测数据是否符合预期设计并决定该操作是否成功。

1.1. 正确性

数据的正确性表现为

  • 从数据内容来看,正确性表现为数据是否符合字段的的数据类型、精度等
  • 从行关系来看,每行数据在表中都是唯一的记录,即可以通过某个特定的条件查询而不会产生歧义
  • 从表关系来看,两个表的主键和外键的数据应保证一致,防止无意义的数据或联表查询错误

1.2. 规则

查询相关资料得知,数据完整性约束的类型可以分为三种类型:

  • 与表有关的约束
  • 域约束
  • 断言

这里要学习的是与表有关的约束,表约束指的是在表的数据列上强制执行的规则,实际上表约束中的某些规则并不陌生,比如not nullprimary key等,下面一一道来。

2. 约束类型

下面使用MYSQL进行相关测试,对应版本为5.7.14

设置约束可以使用关键字,如not nullprimary key等,也可以使用CONSTRAINT语法来指定。

约束 关键字 语句
非空 username varchar(30) not null
主键 id INT PRIMARY KEY CONSTRAINT pk_test_pk PRIMARY KEY(id)
唯一 CONSTRAINT uk_name_pwd UNIQUE(username, password)
外键 uid INT REFERENCES test_foreign(id) CONSTRAINT fk_uid FOREIGN KEY(uid) REFERENCES test_foreign(id)
检查 CONSTRAINT age_more_2 CHECK(age > 2)

2.1. 非空约束

非空约束用来确保某个字段必须有值,使用NOT NULL进行约束声明

CREATE TABLE test_null(
    id INT NOT NULL,
    username VARCHAR(30) NOT NULL,
    sex TINYINT
);    

上面语句指定了idusername字段非空,现在如果插入数据

INSERT INTO test (username, sex) VALUES('txm', 1)     

则控制台会报错

Field 'id' doesn't have a default value

也就是说,有非空约束的字段,要么通过DEFAULT关键字指定默认值,要么就必须在修改该列时确保其数据非空。实际上DEFAULT也是一种约束,被称为默认约束

在大多数MySQL建表规范中,建议将字段均设置为not null。在MySQL中,空字符串是一个有效的值,是占据存储空间的;而NULL不占用任何空间(使用IS NLL进行判断),含有空值的列很难进行查询优化,这里是相关文章的传送门:

2.2. 唯一约束

前面的引子中提到的“保证数据唯一“,用到的就是唯一约束,唯一约束用来指定某个列或某几个列的组合其数据不能重复,

CREATE TABLE test_unique(
    id INT NOT NULL,
    username VARCHAR(30),
    password VARCHAR(20),
    CONSTRAINT uk_name_pwd UNIQUE(username, password)
);

这里定义了usernamepassword的组合数据不能完全相同,即保证不会出现用户名和密码组合完全相同的情况。

我们先插入一条数据

INSERT INTO test_unique(id, username, PASSWORD) VALUES (1, 'txm', '123')

然后再插入一条数据

INSERT INTO test_unique(id, username, PASSWORD) VALUES (2, 'txm', '123')

则控制台会报错

Duplicate entry 'txm-123' for key 'uk_name_pwd'

'txm-123感觉其内部实现就是把多个字段的数据按顺序组合,然后判断其值是否相等,最后决定插入操作是否成功。

2.3. 主键约束

主键应该是最熟悉的一种约束了,它相当于"非空约束 + 唯一约束"的组合。主键能够唯一区分表中的每个行,在MySQL中任何列都何以作为主键,只要他们满足:

  • 任意两行都不具有相同的主键值(不允许重复值)
  • 每行必须有一个主键值,且该值不能为NULL(不允许空值)
CREATE TABLE test_pk(
    id INT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
)

需要注意的是,可以将一列或者多列的组合设置为主键,但是一张表最多只能设置一个主键(也可以不设置主键),否则会报下列错误

Multiple primary key defined

2.4. 检查约束

检查约束可以通过为字段设置一个表达式来检测数据是否符合预期,

CREATE TABLE test_check(
    id INT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    age INT(2) NOT NULL,
    CONSTRAINT age_more_2 CHECK(age > 2)
)

尽管上述约束不会报错,但是为age字段设置为1也可以通过检测,因此检查约束在MySQL中是无效的。查到的一个解释是

从规范中接受这些子句但又忽略子句的原因是为了提高兼容性,以便更容易地从其它SQL服务器中导入代码,并运行应用程序,创建带参考数据的表

因此针对坚持约束,只能从前端解决这些问题了(这里的前端是PHP等服务端,不是Web前端,哈哈)。

2.5. 外键约束

关系型数据库设计的基础是:相同的数据出现多次绝对不是一件好事情。于是一般的做法是将数据拆分成多张表,通过外键的形式将他们关联起来。

外键为某个表中的一列,它包含了另一个表的主键值,定义了两个表之间的关系

-- 主表
CREATE TABLE test_foreign ( 
    id INT PRIMARY KEY, 
    username VARCHAR(30) NOT NULL 
); 
-- 从表
CREATE TABLE test_foreign_sub (
    id INT PRIMARY KEY,
    uid INT,
    money FLOAT(10, 2) NOT NULL
)

外键在联结查询中的使用这里就不谈了,那么什么是外键约束呢?

上面的从表中,从表中的uid字段即为主表中的id主键,但是这只是我们所认为的,在没有任何约束的情况下,数据库只是把他们当作普通的字段而已。试想我们向从表中插入了一条uid在主表中并不存在的数据,或者从主表中删除了某条记录导致从表中的uid无法查询,数据的正确性和完整性就无法保证了。

因此,我们需要将从表的外键和主表的主键关联起来,需要注意的是:主表和从表必须使用InnoDB引擎,MyISAM暂不支持外键约束

CREATE TABLE test_foreign_sub_2 (
    id INT PRIMARY KEY,
    money FLOAT(10, 2) NOT NULL,
    uid INT,
    CONSTRAINT fk_uid FOREIGN KEY(uid) REFERENCES test_foreign(id) --关联外键约束
)

前面提到了从主表中删除某条记录导致从表中的数据残留的问题,也可以通过外键约束来进行处理,即:在删除主表数据时,将从表数据也删除(或者设置为null),这是通过ON DELETE子句来实现的:

  • CASCADE,将从表数据也删除
  • SET NULL将从表数据置为NULL
  • SET DEFAULT,将从表数据设置为默认值
  • NO ACTION,如果删除主表数据影响数据完整性,该操作将被禁止
CREATE TABLE test_foreign_sub_4 (
    id INT PRIMARY KEY,
    money FLOAT(10, 2) NOT NULL,
    uid INT REFERENCES test_foreign(id) ON DELETE CASCADE
)

关于外键更多资料,可以查看:

3. 小结

这里只是总结了约束的一些概念,每种约束都有自己的用处。毫无疑问,使用约束之后,在修改数据的时候效率将会降低,但是得到的是数据正确性的提升,两者如何取舍需要进一步的学习。

从Phaser中了解游戏的几个概念 一次失败的HTML模块化尝试