為順序關係強制執行數據完整性

這些是我的表：

create table trips (
 trip_id serial primary key,
 trip_nm text
);

create table trip_segments (
 segment_id serial primary key,
 departure_ts timestamp with time zone, -- departure time
 arrival_ts   timestamp with time zone, -- arrival time
 trip_id integer references trips (trip_id)
);

插入看起來像：

insert into trips (trip_nm) values ('my trip');

-- first segment
insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                         ('2013-01-30 12:00', '2013-01-30 20:00', trip_id);

-- second segment
insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                         ('2013-01-30 21:00', '2013-01-30 22:00', trip_id);

鑑於上述數據：我想確保第二段的出發時間在第一段的到達時間之後。

這意味著第二段的以下插入應該失敗：

insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                         ('2013-01-30 19:55', '2013-01-31 22:00', trip_id);

一次旅行可能有很多段。有很多旅行。

那麼，我該怎麼做呢？

我的第一個想法是對錶使用check約束函式，trip_segments如下所示：

create function previous_ts(trip_id integer)
 returns timestamp with time zone as $$

 /* do some hackery here using the lag() function */

 return previous_arrival_ts;

$$ language 'sql';

好的，所以我還沒有實現，但這就是想法。但是，我讀到（在 stackoverflow 上，失去了答案）使用check約束函式是一個非常糟糕的主意，而且我是在轉述。

好的，所以必須有更好的方法，也許使用觸發器，我不知道。解決這個問題的最佳方法是什麼？

我正在使用 postgresql 9.5

您可以使用EXCLUSION 約束和 tsrange 數據類型而不是兩個時間戳。

create table trip_segments (
 segment_id serial primary key,
 travel_ts tstzrange, -- departure time to arrival time
 trip_id integer references trips (trip_id),
 EXCLUDE USING gist (trip_id WITH =, travel_ts WITH &&)
);

-- first segment
insert into trip_segments (travel_ts,trip_id) values
                         ('[2013-01-30 12:00,2013-01-30 20:00)', 1);

-- second segment overlaps and fails
insert into trip_segments (travel_ts,trip_id) values
                         ('[2013-01-30 19:55,2013-01-30 22:00]', 1);

你得到：

錯誤：衝突的鍵值違反排除約束“trip_segments_travel_ts_excl”詳細資訊：鍵（travel_ts）=（

$$ “2013-01-30 19:55:00+11”,“2013-01-30 22:00:00+11” $$) 與現有密鑰 (travel_ts)=([“2013-01-30 12:00:00+11”,“2013-01-30 20:00:00+11”)) 衝突。

排除是專門為每個特定的時間範圍設置的trip_id。

insert into trips (trip_nm) values ('their trip'); -- add other trip

-- segment overlaps with trip_id=1, but the exclusion is setup specific to trips
insert into trip_segments (travel_ts,trip_id) values
                         ('[2013-01-30 19:55,2013-01-30 22:00)', 2);

請注意，時間戳範圍不能向後指定（結束時間戳必須等於或高於起始時間戳），它們的邊界是包含 ( []) 或排除 ( ()) 邊界，預設為[)，即包含開始時間和排除結束時間。

引用自：https://dba.stackexchange.com/questions/135230