跳到主要内容

数据表的约束

尽管在 PostgreSQL 的官方文档中，外键（Foreign Key）与主键（Primary Key）都作为约束的一个子章节而存在，但是就我个人的观点而言，它们更加适合与“索引”等部分放到一块，因为约束更多的是对于数据列作出的某种数据规范，而不是别的什么东西。

现在，让我们继续尝试对一个现实中的场景展开分析，假定我们正在编写一款电商软件，需要对各家商店的购物信息展开管理，现在让我们来构造一张简单的数据表，用以描述商店里面的商品：

CREATE TABLE goods (
    good_name TEXT, /* 商品名称 */
    good_id INTEGER, /* 商品ID */
    good_price DOUBLE PRECISION, /* 商品价格 */
    good_type INTEGER, /* 商品类型： 1 --- 自产自销；2 --- 分销代理；3 --- 宣传用非卖品 */
    add_time DATE /* 商品被加入货架的时间 */
);

在这个基础上，我们可以尝试向数据表中插入一些数据：

INSERT INTO cart VALUES ('《原则》', 1, 39.99, 4, CURRENT_DATE);
INSERT INTO cart VALUES ('《C程序设计语言》', 2, 35.88, 2, CURRENT_DATE);
INSERT INTO cart VALUES ('《原则》', 3, NULL, NULL, 35.88, NULL);
提示

CURRENT_DATE, CURRENT_TIME, CUREENT_TIMESTAMP 可以描述当前的时间
在 PostgreSQL 中，我们可以使用 CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAM 等常量与函数描述当前的时间，它们将会按照自身的精确度范围返回对应的结果（如 CURRENT_DATE 将会返回当前的日期，而 CURRENT_TIMESTAMP 将在返回当前日期之外，附带上具体时间与时区等信息）。
使用 SELECT + 对应常量; 尝试查询可以更为直观地体验到它们之间的区别。

可以发现，这些数据存在一些可能会影响我们程序正常执行的情况：

  1. 商品名称与ID存在不对应与重复的情况
    这种重复，可能会造成混淆，进而使得查询返回不恰当的结果，进而影响呈现的最终结果。
  2. 在商品的加入时间列，存在 NULL 数据插入
    在实践中，这种情况可能属于商品过期/被店家撤除，而因为我们的数据表此处并没有提供更为丰富的信息以供我们参考，因此这里的数据记录含义，实际上就变成了“垃圾数据”

所以，我们需要对数据表的结构乃至于插入数据前的质量控制，做出一定的改进工作，这就需要我们思考如下的事情：

  1. 用什么样的方法来标识商品以避免引发歧义？
    使用 good_id 这一数据列用以唯一标识商品（这就需要接下来的 UNIQUE 约束的帮助），同时，我们要对商品名称（good_name）加以限制，避免重名商品上架的情况。
  2. 如何尽力规避垃圾数据的插入？
    设定一定的限制条件，同时对数据表的信息进行完善。
    这就需要我们对数据列进行扩充（使得我们可以了解到更为丰富的信息），同时需要我们借助 NOT NULL 与 CHECK 两种约束的帮助。

在这里，有必要对 PostgreSQL 的这三种约束展开一个介绍，它们从不同的角度限制着数据列所允许插入的数据，进而尽力使得数据库中的数据，契合应用的需要。

  • UNIQUE 约束
    UNIQUE 约束要求所在的数据列数据必须是唯一的（即不允许重复数据的存在），它应用的场景相当丰富，如餐厅菜单（不出现重复的菜），人口系统（不允许身份证号重复）等。
  • CHECK 约束
    CHECK 约束将会按照布尔表达式的要求检查插入的数据，只有符合表达式的数据才可以插入（这种约束非常灵活，常常同应用的实际需要相互结合，因此需要具体情况具体分析）。
  • NOT NULL 约束
    NOT NULL 约束将会要求所在的数据列不得存在 NULL 数据（即不允许空数据的插入），它应用的场景同样丰富，如我们不会允许身份证号为空的数据被记录到监狱管理系统中。

使用约束的方法非常简单，只需要我们在表中数据列的对应位置，加上这些约束的标记，即可以使用这些特性（这就像我们在使用命令行程序的时候，指定某些选项一样）。

现在，让我们逐步根据它们的特性，优化我们的数据表，使其更加符合实际的需要：

为商品ID与商品名称加上 UNIQUE 约束，规避重名问题​

DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
    good_name TEXT UNIQUE, /* 商品名称 */
    good_id INTEGER UNIQUE, /* 商品ID */
    good_price DOUBLE PRECISION, /* 商品价格 */
    good_type INTEGER, /* 商品类型： 1 --- 自产自销；2 --- 分销代理；3 --- 宣传用非卖品 */
    add_time DATE /* 商品被加入货架的时间 */
);

现在，让我们再次尝试加入一些重复的数据，可以发现，这一次 PostgreSQL 将会拒绝我们插入这样的数据：

INSERT INTO goods (good_name, good_id) VALUES ('<原则>', 1);
INSERT INTO goods (good_name, good_id) VALUES ('<贞观政要>', 2);
/* 让我们尝试在不同的键值处引入重复数据 */
INSERT INTO goods (good_name, good_id) VALUES ('<原则>', 3);
INSERT INTO goods (good_name, good_id) VALUES ('<中国通史>', 2);

这些 SQL 语句的运行结果如下所示：

unique-result

可以发现，PostgreSQL 将会拒绝我们一部分的数据插入，提示错误（duplicate key value violates unique constraint "对应数据列"），并且给出具体分析（某个键值处的某个数据已经存在）

为商品插入日期数据列补充 NOT NULL 约束，确保非空​

商品插入日期对于很多场景下面的需求而言，是非常重要的一项参考指标，比如我们希望清理在某个日期之前的过期商品时，这项数据就非常有用。

而为空（NULL）的时候，就可能存在一定的问题，现在让我们加上这项约束，规避这个可能的风险。

DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
    good_name TEXT UNIQUE, /* 商品名称 */
    good_id INTEGER UNIQUE, /* 商品ID */
    good_price DOUBLE PRECISION, /* 商品价格 */
    good_type INTEGER, /* 商品类型： 1 --- 自产自销；2 --- 分销代理；3 --- 宣传用非卖品 */
    add_time DATE NOT NULL /* 商品被加入货架的时间 */
);

现在，让我们再次尝试插入一定的数据：

/* 刻意插入 NULL 数据  */
INSERT INTO goods (add_time) VALUES (NULL);
/* 插入其它列数据，忽略 add_time 列 */
INSERT INTO goods (good_name) VALUES ('《原则》');

两条插入都将会宣告失败，截图如下所示：

not-null-result

提示

使用 DEFAULT 为数据列设立默认值
规避 NOT NULL 约束报错，除了在我们插入数据时下功夫之外，还可以通过 DEFAULT 指令为数据列设立默认值来进行解决，方式就是在原有基础上添加 DEFAULT 数据。

如，我们可以把上面的建表代码调整为：

DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
    good_name TEXT UNIQUE, /* 商品名称 */
    good_id INTEGER UNIQUE, /* 商品ID */
    good_price DOUBLE PRECISION, /* 商品价格 */
    good_type INTEGER, /* 商品类型： 1 --- 自产自销；2 --- 分销代理；3 --- 宣传用非卖品 */
    add_time DATE NOT NULL DEFAULT CURRENT_DATE /* 商品被加入货架的时间 */
);

这样，在我们没有指定 add_time 的时候，CURRENT_DATE 将会代替 NULL 被填入数据表，进而帮助我们通过 NOT NULL 约束的检查。

结合业务需求，补充 CHECK 约束，优化数据质量​

相较于通用性强的 NOT NULL 与 UNIQUE 约束，CHECK 约束同业务需求联系更为紧密一些，它的使用方法如下：

CHECK /* 布尔表达式  */

比如，我们希望确保我们商品的价格为正数，就可以使用如下的代码：

DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
    good_name TEXT UNIQUE, /* 商品名称 */
    good_id INTEGER UNIQUE, /* 商品ID */
    good_price DOUBLE PRECISION CHECK (good_price > 0), /* 商品价格 */
    good_type INTEGER , /* 商品类型： 1 --- 自产自销；2 --- 分销代理；3 --- 宣传用非卖品 */
    add_time DATE NOT NULL DEFAULT CURRENT_DATE/* 商品被加入货架的时间 */
);

现在，让我们尝试插入一条负数数据：

INSERT INTO goods (good_price) VALUES (-1);

PostgreSQL 将会拒绝这条数据的插入，并提示我们违反了约束要求：

check-result

而如法炮制，我们同样可以针对 good_type 展开同样的约束以进行数据质量的管理，这里可以尝试自己动手，以展开验证。