履歴テーブル設計パターン5選｜有効開始日・終了日の持ち方とSQL

履歴テーブルとは、データが「いつからいつまで、どんな値だったか」を後から取り出せるように残すためのテーブルです。 持ち方は大きく5つあり、どれを選ぶかは「現在の値を読むことが多いのか」「過去や未来の時点の値を読みたいのか」で決まります。

この記事では5つのパターンすべてについて、CREATE TABLE・現在値を取るSQL・時点を指定するSQLを実際に流し、返ってきた結果とエラー文をそのまま載せています。期間の重複をデータベースに弾かせる方法と、終了日の持ち方の比較も、同じデータで確かめました。

確認環境：PostgreSQL 16.15（Ubuntu 24.04）、MySQL 8.0.46。確認日は 2026-10-05。MariaDB・SQL Server・PostgreSQL 18 の内容は、同日に開いた各公式ドキュメントの記載です（記事末尾に出典）。

この記事の結論

• 現在値を読むことが多く、履歴はたまに見るだけなら、本体テーブルと履歴テーブルを分ける（パターン1か4）。
• 過去の時点の値を取り出したい、来月からの値を先に登録したいなら、有効開始日・有効終了日を持つ（パターン3）。
• 有効期間を持つなら、期間は「開始日を含み、終了日を含まない」半開区間にし、重複はデータベースの制約で弾く。
• 終了日は NOT NULL で 9999-12-31 を入れると、時点指定のSQLが1通りで済む。
• 「誰が何をしたか」を残したいなら監査ログ、「消したことにしたい」なら論理削除で、履歴テーブルとは目的が別。

履歴テーブル設計パターン5選の早見表

どのパターンを選ぶかの判断基準

迷ったときは、「未来の日付の行を入れる予定があるか」を先に確かめます。あるならパターン3、ないなら本体と履歴を分ける形が扱いやすくなります。

パターン3とパターン5はどちらも「期間」を持ちますが、意味が異なります。パターン3の期間は業務上その値が有効な期間で、利用者が決めます。パターン5の期間はその行がデータベースに存在していた期間で、データベースが決めます。

以下、商品の単価を題材に1つずつ見ていきます。

パターン1：上書き＋履歴表

本体テーブルは最新の値だけを持ち、更新する直前の行を履歴表へ写します。

CREATE TABLE item (
  item_id    integer PRIMARY KEY,
  item_name  text    NOT NULL,
  price      integer NOT NULL CHECK (price >= 0),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE item_history (
  history_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  item_id     integer NOT NULL,
  item_name   text    NOT NULL,
  price       integer NOT NULL,
  valid_from  timestamptz NOT NULL,
  valid_to    timestamptz NOT NULL,
  CHECK (valid_from < valid_to)
);
CREATE INDEX item_history_idx ON item_history (item_id, valid_from);

INSERT INTO item VALUES (1, 'ノートA4', 300, '2026-04-01 00:00+09');

-- 更新のたびに、変更前の行を履歴へ写してから上書きする（同じトランザクション）
BEGIN;
INSERT INTO item_history (item_id, item_name, price, valid_from, valid_to)
SELECT item_id, item_name, price, updated_at, '2026-07-01 00:00+09' FROM item WHERE item_id = 1;
UPDATE item SET price = 330, updated_at = '2026-07-01 00:00+09' WHERE item_id = 1;
COMMIT;

同じ手順で 2026-10-01 に 350 へ変更したあとの状態で、現在値と時点指定の値を取ります。

-- 現在値
SELECT item_id, price FROM item WHERE item_id = 1;

-- 2026-08-15 時点の値
SELECT item_id, price FROM item
 WHERE item_id = 1 AND updated_at <= '2026-08-15 00:00+09'
UNION ALL
SELECT item_id, price FROM item_history
 WHERE item_id = 1 AND valid_from <= '2026-08-15 00:00+09' AND '2026-08-15 00:00+09' < valid_to;

 item_id | price
---------+-------
       1 |   350
(1 row)

 item_id | price
---------+-------
       1 |   330
(1 row)

現在値は主キーで1行引くだけなので速く、SQLも短くなります。時点指定は本体と履歴表の両方を見る必要があるため、UNION ALL が要ります。履歴表への INSERT をアプリのどこか1か所で書き忘れると履歴が欠けるので、更新処理を1つの関数にまとめておくと安定します。

パターン2：全件追記

UPDATE を使わず、変更のたびに版番号を1つ増やした行を INSERT します。

CREATE TABLE item_version (
  item_id    integer NOT NULL,
  version_no integer NOT NULL,
  item_name  text    NOT NULL,
  price      integer NOT NULL CHECK (price >= 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (item_id, version_no)
);
INSERT INTO item_version VALUES
 (1, 1, 'ノートA4', 300, '2026-04-01 00:00+09'),
 (1, 2, 'ノートA4', 330, '2026-07-01 00:00+09'),
 (1, 3, 'ノートA4', 350, '2026-10-01 00:00+09'),
 (2, 1, 'ボールペン黒', 120, '2026-04-01 00:00+09');

-- 現在値（キーごとに最新の版）
SELECT DISTINCT ON (item_id) item_id, version_no, price
  FROM item_version
 ORDER BY item_id, version_no DESC;

-- 2026-08-15 時点の値
SELECT DISTINCT ON (item_id) item_id, version_no, price
  FROM item_version
 WHERE created_at <= '2026-08-15 00:00+09'
 ORDER BY item_id, version_no DESC;

 item_id | version_no | price
---------+------------+-------
       1 |          3 |   350
       2 |          1 |   120
(2 rows)

 item_id | version_no | price
---------+------------+-------
       1 |          2 |   330
       2 |          1 |   120
(2 rows)

DISTINCT ON は PostgreSQL の書き方です。MySQL 8.0 では ROW_NUMBER() で1番目の行を取ります（MySQL 8.0.46 で同じ結果になりました）。

SELECT item_id, version_no, price
  FROM (SELECT v.*, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY version_no DESC) AS rn
          FROM item_version v) s
 WHERE rn = 1;

主キーを（item_id, version_no）にしておくと、2人が同時に同じ版番号で登録しようとしたとき、後から来たほうが止まります。

ERROR:  duplicate key value violates unique constraint "item_version_pkey"
DETAIL:  Key (item_id, version_no)=(1, 3) already exists.

このエラーを受けたら最新の版を読み直してやり直す、という形にすれば、同時更新の検出にも使えます。現在値を取るたびに「最新の版」を探す必要があるので、参照が多い表では最新だけを写したビューや別表を用意します。

パターン3：有効期間（有効開始日・有効終了日）

1行ごとに「いつから」「いつまで」を持たせます。未来の日付の行を先に入れておけるのが、ほかのパターンに無い特長です。

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE item_price (
  item_id    integer NOT NULL,
  price      integer NOT NULL CHECK (price >= 0),
  valid_from date    NOT NULL,
  valid_to   date    NOT NULL DEFAULT '9999-12-31',
  CHECK (valid_from < valid_to),
  PRIMARY KEY (item_id, valid_from),
  CONSTRAINT item_price_no_overlap
    EXCLUDE USING gist (item_id WITH =, daterange(valid_from, valid_to, '[)') WITH &&)
);

INSERT INTO item_price VALUES
 (1, 300, '2026-04-01', '2026-07-01'),
 (1, 330, '2026-07-01', '2026-10-01'),
 (1, 350, '2026-10-01', '2027-01-01'),
 (1, 380, '2027-01-01', '9999-12-31');   -- 来年からの値を先に登録

valid_to は「その日の前日まで有効」という意味です（半開区間）。前の行の valid_to と次の行の valid_from が同じ日付になります。

-- 現在値（実行日は 2026-10-05）
SELECT item_id, price, valid_from, valid_to FROM item_price
 WHERE item_id = 1 AND valid_from <= CURRENT_DATE AND CURRENT_DATE < valid_to;

-- 2026-08-15 時点の値
SELECT item_id, price, valid_from, valid_to FROM item_price
 WHERE item_id = 1 AND valid_from <= DATE '2026-08-15' AND DATE '2026-08-15' < valid_to;

-- 2027-02-01 時点の値（予約済みの値）
SELECT item_id, price, valid_from, valid_to FROM item_price
 WHERE item_id = 1 AND valid_from <= DATE '2027-02-01' AND DATE '2027-02-01' < valid_to;

 item_id | price | valid_from |  valid_to
---------+-------+------------+------------
       1 |   350 | 2026-10-01 | 2027-01-01
(1 row)

 item_id | price | valid_from |  valid_to
---------+-------+------------+------------
       1 |   330 | 2026-07-01 | 2026-10-01
(1 row)

 item_id | price | valid_from |  valid_to
---------+-------+------------+------------
       1 |   380 | 2027-01-01 | 9999-12-31
(1 row)

現在値・過去・未来のどれも、日付を差し替えるだけの同じSQLで取れます。現在値を何度も読むなら、ビューにしておくと呼び出し側が短くなります。

CREATE VIEW item_price_current AS
SELECT item_id, price, valid_from, valid_to FROM item_price
 WHERE valid_from <= CURRENT_DATE AND CURRENT_DATE < valid_to;

期間の重複を排他制約（EXCLUDE）で弾く

上の DDL にある EXCLUDE が、同じ item_id で期間が重なる行を止める制約です。item_id WITH = は「item_id が等しく」、daterange(...) WITH && は「期間が重なる」という意味で、両方を満たす2行は共存できません。整数の列を GiST 索引に入れるために btree_gist 拡張を使います。

重なる期間を入れてみます。

INSERT INTO item_price VALUES (1, 340, '2026-09-15', '2026-11-01');

ERROR:  conflicting key value violates exclusion constraint "item_price_no_overlap"
DETAIL:  Key (item_id, daterange(valid_from, valid_to, '[)'::text))=(1, [2026-09-15,2026-11-01)) conflicts with existing key (item_id, daterange(valid_from, valid_to, '[)'::text))=(1, [2026-07-01,2026-10-01)).

どの行とぶつかったかが DETAIL に出ます。開始日と終了日が逆の行は、CHECK 制約のほうで止まります。

INSERT INTO item_price VALUES (2, 100, '2026-09-15', '2026-09-01');

ERROR:  new row for relation "item_price" violates check constraint "item_price_check1"
DETAIL:  Failing row contains (2, 100, 2026-09-15, 2026-09-01).

値を変更する手順：今の行を閉じてから、次の行を開く

値を変えるときは、有効中の行の valid_to を切り替え日に更新し、その日から始まる行を追加します。順番が大切です。

-- item_id = 2 は (120: 04-01〜10-01) (130: 10-01〜9999-12-31) の2行がある状態
BEGIN;
UPDATE item_price SET valid_to = '2026-12-01' WHERE item_id = 2 AND valid_to = '9999-12-31';
INSERT INTO item_price VALUES (2, 140, '2026-12-01', '9999-12-31');
COMMIT;

SELECT * FROM item_price WHERE item_id = 2 ORDER BY valid_from;

 item_id | price | valid_from |  valid_to
---------+-------+------------+------------
       2 |   120 | 2026-04-01 | 2026-10-01
       2 |   130 | 2026-10-01 | 2026-12-01
       2 |   140 | 2026-12-01 | 9999-12-31
(3 rows)

今の行を閉じずに次の行を入れると、排他制約が止めてくれます。

INSERT INTO item_price VALUES (2, 150, '2027-03-01', '9999-12-31');

ERROR:  conflicting key value violates exclusion constraint "item_price_no_overlap"
DETAIL:  Key (item_id, daterange(valid_from, valid_to, '[)'::text))=(2, [2027-03-01,9999-12-31)) conflicts with existing key (item_id, daterange(valid_from, valid_to, '[)'::text))=(2, [2026-12-01,9999-12-31)).

期間のすき間を見つけるSQL

排他制約が止めるのは重複だけで、すき間（どの行にも入らない日）は通ります。すき間を許さない業務なら、次のSQLを定期的に流して確かめます。

-- item_id = 3 に、6月がどの行にも入らないデータを入れてある
INSERT INTO item_price VALUES (3, 500, '2026-04-01', '2026-06-01'), (3, 550, '2026-07-01', '9999-12-31');

SELECT item_id, valid_to AS gap_from, next_from AS gap_to
  FROM (SELECT item_id, valid_to,
               lead(valid_from) OVER (PARTITION BY item_id ORDER BY valid_from) AS next_from
          FROM item_price) s
 WHERE next_from IS NOT NULL AND next_from <> valid_to;

 item_id |  gap_from  |   gap_to
---------+------------+------------
       3 | 2026-06-01 | 2026-07-01
(1 row)

パターン4：トリガー記録

本体への INSERT・UPDATE・DELETE をトリガーが拾い、履歴表へ書き込みます。アプリのSQLは変えずに済みます。題材を会員のランクに変えて示します。

CREATE TABLE member (
  member_id  integer PRIMARY KEY,
  rank       text NOT NULL,
  email      text NOT NULL
);
CREATE TABLE member_history (
  history_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  op         char(1) NOT NULL CHECK (op IN ('I','U','D')),
  changed_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  member_id  integer NOT NULL,
  rank       text,
  email      text
);
CREATE FUNCTION member_history_fn() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO member_history (op, member_id, rank, email) VALUES ('D', OLD.member_id, OLD.rank, OLD.email);
    RETURN OLD;
  END IF;
  INSERT INTO member_history (op, member_id, rank, email)
  VALUES (left(TG_OP, 1), NEW.member_id, NEW.rank, NEW.email);
  RETURN NEW;
END $$;
CREATE TRIGGER member_history_trg
AFTER INSERT OR UPDATE OR DELETE ON member
FOR EACH ROW EXECUTE FUNCTION member_history_fn();

INSERT INTO member VALUES (10, 'レギュラー', 'hanako@example.com');
UPDATE member SET rank = 'ゴールド' WHERE member_id = 10;
UPDATE member SET email = 'hanako2@example.com' WHERE member_id = 10;
DELETE FROM member WHERE member_id = 10;

SELECT history_id, op, member_id, rank, email FROM member_history ORDER BY history_id;

 history_id | op | member_id |    rank    |        email
------------+----+-----------+------------+---------------------
          1 | I  |        10 | レギュラー | hanako@example.com
          2 | U  |        10 | ゴールド   | hanako@example.com
          3 | U  |        10 | ゴールド   | hanako2@example.com
          4 | D  |        10 | ゴールド   | hanako2@example.com
(4 rows)

現在値は本体を SELECT ... FROM member WHERE member_id = 10 で引きます。時点指定は、履歴表から「その時点以前で最新の行」を取ります（下は2件目の変更が終わった時点）。

SELECT DISTINCT ON (member_id) member_id, op, rank, email
  FROM member_history WHERE history_id <= 2
 ORDER BY member_id, history_id DESC;

 member_id | op |   rank   |       email
-----------+----+----------+--------------------
        10 | U  | ゴールド | hanako@example.com
(1 row)

時刻で絞るなら WHERE changed_at <= '2026-08-15 00:00+09' に替えます。op が D の行が返ってきたら、その時点では削除済みという意味です。

行単位のトリガーは TRUNCATE では動きません。上の表で TRUNCATE member; を実行したあとも、履歴表の件数は4件のままでした。TRUNCATE を使う運用がある場合は、権限で TRUNCATE を許可しない設定にしておくと履歴がそろいます。

パターン5：システムバージョン管理テーブル

SQL:2011 で標準化された機能で、テーブルに指定を付けるだけで、データベースが変更前の行と期間を自動で保持します。MariaDB と SQL Server が対応しています。以下の構文は各公式ドキュメントに載っているものです。

MariaDB（公式ドキュメントの例）：

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING;

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';
SELECT * FROM t FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00';
SELECT * FROM t FOR SYSTEM_TIME ALL;

SQL Server（Microsoft Learn の例）：

CREATE TABLE dbo.Employee
(
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR (100) NOT NULL,
    [Position] VARCHAR (100) NOT NULL,
    [Department] VARCHAR (100) NOT NULL,
    [Address] NVARCHAR (1024) NOT NULL,
    [AnnualSalary] DECIMAL (10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

現在値はふつうの SELECT で取れ、過去の状態は FOR SYSTEM_TIME 句で取ります。SQL Server の FOR SYSTEM_TIME には AS OF、FROM … TO、BETWEEN … AND、CONTAINED IN、ALL の5つの指定があります。

この機能が記録するのはデータベースに書き込まれた時刻です。「来月から有効」という業務上の日付は表せないので、予約変更が要るならパターン3と組み合わせます。

PostgreSQL 16 と MySQL 8.0 で同じ構文を流すと、どちらも構文エラーになりました。この2つではパターン4のトリガーで同じ役割を持たせます。

-- PostgreSQL 16.15
ERROR:  syntax error at or near "SYSTEM"
LINE 1: CREATE TABLE t_sv (x integer) WITH SYSTEM VERSIONING;
                                           ^
-- MySQL 8.0.46
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SYSTEM VERSIONING' at line 1

終了日は NULL・9999-12-31・半開区間のどれにするか

有効期間を持つときに決めることは2つあります。「まだ終わりが決まっていない行の終了日に何を入れるか」と「終了日を期間に含めるか」です。

終わりが決まっていない行：NULL か 9999-12-31 か

終了日を NULL にした表で、時点指定のSQLをそのまま流してみます。

CREATE TABLE price_null (item_id int, price int, valid_from date NOT NULL, valid_to date);
INSERT INTO price_null VALUES (1,300,'2026-04-01','2026-07-01'),(1,330,'2026-07-01',NULL);

-- (a) NULL を考えずに書いた条件
SELECT price FROM price_null
 WHERE item_id=1 AND valid_from <= DATE '2026-08-15' AND DATE '2026-08-15' < valid_to;
-- (b) NULL を「終わりなし」として扱う条件
SELECT price FROM price_null
 WHERE item_id=1 AND valid_from <= DATE '2026-08-15' AND (valid_to IS NULL OR DATE '2026-08-15' < valid_to);
-- (c) 範囲型に任せる
SELECT price FROM price_null
 WHERE item_id=1 AND daterange(valid_from, valid_to, '[)') @> DATE '2026-08-15';

 price
-------
(0 rows)

 price
-------
   330
(1 row)

 price
-------
   330
(1 row)

(a) は0行になります。日付 < NULL が真にならないためです。NULL を使う設計では、すべてのSQLに valid_to IS NULL OR を書くか、PostgreSQL の範囲型（上限 NULL は「終わりなし」として扱われる）を使う必要があります。

9999-12-31 に1日足したときの動きは、データベースで違いました。

-- PostgreSQL 16： SELECT DATE '9999-12-31' + 1;
  next_day
-------------
 10000-01-01

-- MySQL 8.0： SELECT DATE '9999-12-31' + INTERVAL 1 DAY;
+----------+
| next_day |
+----------+
| NULL     |
+----------+
Warning 1441: Datetime function: datetime field overflow

MySQL では NULL と警告になります。「終了日の翌日」を計算する処理がある場合は、9999-12-31 の行を計算の対象から外しておきます。

終了日を含めるか：閉区間と半開区間

「6月30日まで有効」を valid_to = 2026-06-30 と持つのが閉区間、valid_to = 2026-07-01 と持つのが半開区間です。日付だけを扱っている間はどちらでも同じ結果になりますが、時刻つきの値で検索したときに差が出ました。

CREATE TABLE price_closed (item_id int, price int, valid_from date NOT NULL, valid_to date NOT NULL);
INSERT INTO price_closed VALUES (1,300,'2026-04-01','2026-06-30'),(1,330,'2026-07-01','9999-12-31');

-- 閉区間：6月30日の15時で検索
SELECT price FROM price_closed
 WHERE item_id=1 AND TIMESTAMP '2026-06-30 15:00' BETWEEN valid_from AND valid_to;

-- 半開区間（パターン3の item_price）：同じ時刻で検索
SELECT price FROM item_price
 WHERE item_id=1 AND valid_from <= TIMESTAMP '2026-06-30 15:00' AND TIMESTAMP '2026-06-30 15:00' < valid_to;

 price
-------
(0 rows)

 price
-------
   300
(1 row)

閉区間では、6月30日の0時より後から7月1日の0時までが、どの行にも入らない時間帯になります。半開区間なら前の行の終わりと次の行の始まりが同じ値なので、すき間ができません。

PostgreSQL の daterange は、閉区間で作っても半開区間に直して保持されます。隣り合う半開区間どうしは「重なっていない」と判定されるので、排他制約とそのまま組み合わせられます。

SELECT daterange('2026-04-01','2026-07-01','[)') && daterange('2026-07-01','2026-10-01','[)') AS half_open,
       daterange('2026-04-01','2026-06-30','[]') AS closed_canon,
       daterange('2026-04-01',NULL) AS open_end;

 half_open |      closed_canon       |   open_end
-----------+-------------------------+---------------
 f         | [2026-04-01,2026-07-01) | [2026-04-01,)

DB別の違い：重複の防ぎ方と標準機能

PostgreSQL 18 と MariaDB・SQL Server の欄は、公式ドキュメントの記載です。PostgreSQL 18 のドキュメントには、UNIQUE (id, valid_at WITHOUT OVERLAPS) が EXCLUDE USING GIST (id WITH =, valid_at WITH &&) と同じように働くこと、対象の列は範囲型か複数範囲型であること、範囲型でない列を含めるときは btree_gist 拡張を使うことが書かれています。

MySQL 8.0 で期間の重複を防ぐ（トリガー）

MySQL 8.0 には排他制約が無いので、BEFORE INSERT トリガーで重なる行を探し、あれば SIGNAL でエラーにします。重なりの条件は「相手の開始 < 自分の終了、かつ、自分の開始 < 相手の終了」です。

CREATE TABLE item_price (
  item_id    INT  NOT NULL,
  price      INT  NOT NULL,
  valid_from DATE NOT NULL,
  valid_to   DATE NOT NULL DEFAULT '9999-12-31',
  PRIMARY KEY (item_id, valid_from),
  CONSTRAINT chk_period CHECK (valid_from < valid_to)
);

DELIMITER //
CREATE TRIGGER item_price_no_overlap_bi BEFORE INSERT ON item_price
FOR EACH ROW
BEGIN
  IF EXISTS (SELECT 1 FROM item_price p
              WHERE p.item_id = NEW.item_id
                AND p.valid_from < NEW.valid_to
                AND NEW.valid_from < p.valid_to) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'item_price: period overlaps';
  END IF;
END//
DELIMITER ;

INSERT INTO item_price VALUES (1, 300, '2026-04-01', '2026-07-01'), (1, 330, '2026-07-01', '9999-12-31');
INSERT INTO item_price VALUES (1, 340, '2026-09-15', '2026-11-01');   -- 重なる
INSERT INTO item_price VALUES (2, 100, '2026-09-15', '2026-09-01');   -- 開始と終了が逆

ERROR 1644 (45000) at line 25: item_price: period overlaps
ERROR 3819 (HY000) at line 26: Check constraint 'chk_period' is violated.

時点指定のSQLは PostgreSQL と同じ形で動きます。

SELECT item_id, price, valid_from, valid_to FROM item_price
 WHERE item_id = 1 AND valid_from <= DATE '2026-08-15' AND DATE '2026-08-15' < valid_to;

+---------+-------+------------+------------+
| item_id | price | valid_from | valid_to   |
+---------+-------+------------+------------+
|       1 |   330 | 2026-07-01 | 9999-12-31 |
+---------+-------+------------+------------+

このトリガーは INSERT だけを見ています。valid_to を UPDATE で延ばす操作も確かめるなら、同じ条件に「自分自身の行を除く」を足した BEFORE UPDATE トリガーも用意します。また、トリガーは同時に走る別のトランザクションがまだ確定していない行を見られないので、同じ item_id を同時に変更する可能性がある場合は、親の行を SELECT ... FOR UPDATE でロックしてから登録する手順にします。

PostgreSQL から MySQL へ、またはその逆へ移すときは、次の対応で読み替えます。

履歴テーブル・監査ログ・論理削除の使い分け

3つは「過去の情報を残す」点が似ていますが、答えたい問いが違います。

確かめて分かったこと

• 終了日が NULL の行は、NULL を考えない条件では0行になる。 日付 < valid_to だけでは有効中の行が返らず、valid_to IS NULL OR か範囲型が必要でした。
• 閉区間は、日付型の列に時刻つきの値で検索すると最終日が抜ける。 2026-06-30 15:00 での検索が0行でした。半開区間では同じ検索で1行返りました。
• 排他制約は「今の行を閉じる前に次の行を入れる」順番の取り違えも止める。 重複データの混入だけでなく、手順の取り違えにもその場で気づけます。
• 排他制約はすき間を止めない。 6月がどの行にも入らないデータはそのまま入りました。すき間は lead() を使った検査SQLで見つけます。
• 9999-12-31 の翌日は、PostgreSQL では 10000-01-01、MySQL では NULL と警告になる。 終了日に日数を足す処理があるかを設計時に確かめておくと安心です。
• 行トリガーは TRUNCATE では動かない。 TRUNCATE 後も履歴表は4件のままで、消えたことの記録は残りませんでした。

よくある質問

Q. 履歴テーブルとは何ですか？ A. データの変更前の値や、値が有効だった期間を残しておくテーブルです。本体テーブルと分けて持つ形（パターン1・4）と、1つのテーブルに期間や版番号を持たせる形（パターン2・3）があります。

Q. 有効開始日・有効終了日と、作成日時・更新日時の違いは？ A. 有効開始日・終了日は業務上その値を使う期間で、未来の日付も入ります。作成日時・更新日時は行を書き込んだ時刻で、常に過去か現在です。「10月1日から値上げ」を9月に登録するとき、有効開始日は10月1日、作成日時は9月の登録時刻になります。

Q. 有効終了日は NULL と 9999-12-31 のどちらがよいですか？ A. SQLを1通りにしたいなら 9999-12-31 です。NULL は「未定」という意味がそのまま伝わる一方、すべての条件に IS NULL の分岐が要ります。PostgreSQL だけで使うなら、範囲型に NULL を渡す形も選べます。

Q. 期間が重複して登録できないときはどうすればよいですか？ A. 有効中の行の終了日を先に切り替え日へ更新してから、新しい行を追加します。エラーの DETAIL にぶつかった相手の期間が出るので、その行の valid_to を確かめます。

Q. 履歴テーブルと監査ログの違いは？ A. 履歴テーブルは「値」を再現するため、監査ログは「操作」を追うためのものです。履歴テーブルはアプリが業務の計算で読み、監査ログは管理者が調査で読みます。

まとめ

• 現在値の参照が中心なら本体＋履歴表（パターン1・4）、過去時点の参照や予約変更が要るなら有効開始日・終了日（パターン3）。
• 有効期間は半開区間（開始日を含み、終了日を含まない）にすると、すき間ができず、連続の確認も「前の終了日＝次の開始日」で済む。
• 終わりが決まっていない行は NOT NULL の 9999-12-31 にすると、時点指定のSQLが1通りになる。
• PostgreSQL では EXCLUDE 制約で期間の重複を弾ける。MySQL 8.0 ではトリガーと SIGNAL で同じ確認をする。
• システムバージョン管理は MariaDB と SQL Server の標準機能で、記録するのはDBへの書き込み時刻。業務上の有効日とは別のもの。
• 「誰が何をしたか」は監査ログ、「消したことにする」は論理削除。答えたい問いで使い分ける。

参考・出典

• PostgreSQL 16 Documentation「8.17. Range Types」（Constraints on Ranges） https://www.postgresql.org/docs/16/rangetypes.html
• PostgreSQL 18 Documentation「CREATE TABLE」（WITHOUT OVERLAPS、PERIOD） https://www.postgresql.org/docs/18/sql-createtable.html
• MariaDB Documentation「System-Versioned Tables」 https://mariadb.com/kb/en/system-versioned-tables/
• Microsoft Learn「Temporal tables」 https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver17

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。