时间戳

首页 > 解决方案 > 数据库约束检查值（来自不同行）是否按连续顺序排列

database - 数据库约束检查值（来自不同行）是否按连续顺序排列

问题描述

我正在设置一个 PostgreSQL 数据库，并且想要创建一个约束来检查值（来自单个表中的不同行）是否是连续的。

该表如下所示：

+-------------+---------+-------+
| waypoint_id | path_id | order |
+-------------+---------+-------+
|          89 |       1 |     1 |
|          16 |       1 |     2 |
|          17 |       1 |     3 |
|          19 |       1 |     4 |
|           4 |       1 |     5 |
|          75 |       2 |     1 |
|          28 |       2 |     2 |
|           2 |       2 |     3 |
+-------------+---------+-------+

它是一个表格，用于存储某条路径的航点顺序。

约束必须检查 order 列中的值（具有相等的 path_id）是否是连续的。

这是一个无效的示例：（顺序不连续，因为缺少 3）

+-------------+---------+-------+
| waypoint_id | path_id | order |
+-------------+---------+-------+
|          21 |       1 |     1 |
|          29 |       1 |     2 |
|         104 |       1 |     4 |
+-------------+---------+-------+

我希望约束不允许插入该行：

 |         104 |       1 |     4 |

请向我展示类似问题的解决方案示例，或向我指出如何解决问题的文档。

标签： databasepostgresqlconstraints

解决方案

给你：https ://dbfiddle.uk/?rdbms=postgres_11&fiddle=b67be9527e86fd444d158f9ab93bf600

对于不耐烦的人：

CREATE OR REPLACE FUNCTION consecutive_check()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW."order" = 1 OR EXISTS (SELECT 1 FROM my_table WHERE path_id = NEW.path_id AND "order" = (NEW."order" - 1)) THEN
        RETURN NEW;
    END IF;

    RAISE EXCEPTION 'Previous waypoint not available for path_id = %', NEW.path_id;
END;
$function$;

CREATE TRIGGER no_holes_path
    BEFORE INSERT
    ON my_table
    FOR EACH ROW
    EXECUTE PROCEDURE consecutive_check()
;

警告：这将导致查询表，因此您必须有一个索引path_id，order并且您必须意识到此解决方案无法很好地扩展这一事实。

推荐阅读