Database constraint checking if values (from different rows) are in consecutive order

Question

I'm setting up a PostgreSQL database, and want to create a constraint for checking if values (from different rows in a single table) are consecutive.

The table looks like this:

+-------------+---------+-------+
| 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 |
+-------------+---------+-------+

It's a table for storing the order of waypoints of a certain path.

The waypoint_id is a ForeignKey to a table where the coordinates of the waypoints are stored.
The path_id is a ForeignKey to a table where info about the path is stored (what type of path etc).
The order is an integer that stores the order of the waypoints in a specific path.
The PrimaryKey is a composite of all 3 columns.

The constraint has to check if the values in the order column (with equal path_id) are consecutive.

This is an INVALID example: (the order is not consecutive because the 3 is missing)

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

I expect a constraint to not allow the INSERT of this row:

 |         104 |       1 |     4 |

Please show me examples of solutions for similar problems or point me to documentation on how to do his.

Note: your model allows a waypoint to occur more than once on a path, creating cycles. Do you want to allow cycles? Second: `order` is a reserved word; better not use it as a columnname. Third: *why* does the `order`need to be consecutive? IMO it only needs to be *orderable* — wildplasser, May 11 '19 at 18:37
Yes I want to allow a waypoint to occur multiple times. Second: thanks, I will change that. Third: I am not sure what orderable means exactly (and Google doesn't tell me either). The order always starts at 1 and counts up from there, without skipping numbers. Is this orderable? If not, what is orderable exactly? — Jim van Lienden, May 12 '19 at 10:56

score 1 · Accepted Answer · answered May 11 '19 at 18:23

Here you go: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=b67be9527e86fd444d158f9ab93bf600

For the impatient:

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()
;

WARNING: This will result in the table being queried, so you must have an index over path_id and order and you must be aware of the fact that this solution will not scale well.

score 0 · Answer 2 · answered May 12 '19 at 14:44

The candidate key for this table is (track_id, "order"); the waypoint_id doesnot need tobe included.
(you could add an extra unique constraint on (track_id, waypoint_id)to prevent points to be visited twice on a single track)
the gapless constraint is notneeded, you only need order and (per track) uniqueness on order
ORDER is a keyword; better avoid it as an identifier.

CREATE TABLE tracks
        (track_id INTEGER NOT NULL
        , step INTEGER NOT NULL
        , waypoint_id INTEGER -- REFERENCES waypoints(id)
        , PRIMARY KEY (track_id,step)
        );

INSERT INTO tracks(track_id, step, waypoint_id)VALUES
(1,1,89) , (1,2,16) , (1,4,17), (1,5,19), (1,6,4)       -- mind the gap!
, (2,11,75) , (2,22,28) , (2,44,2)                      -- Large gaps!
        ;

CREATE VIEW tracks_ordered AS
SELECT track_id
        , rank() OVER (PARTITION BY track_id ORDER BY step) AS "Order!"
        , waypoint_id
FROM tracks;

SELECT *FROM tracks_ordered;

Result:

 track_id | Order! | waypoint_id 
----------+--------+-------------
        1 |      1 |          89
        1 |      2 |          16
        1 |      3 |          17
        1 |      4 |          19
        1 |      5 |           4
        2 |      1 |          75
        2 |      2 |          28
        2 |      3 |           2
(8 rows)

see also: https://stackoverflow.com/questions/14085258/how-do-i-get-a-column-with-consecutive-increasing-numbers-without-having-any-n/14092775#14092775 for bad advice — wildplasser, May 13 '19 at 19:36

Database constraint checking if values (from different rows) are in consecutive order

2 Answers2