隣接リストの木構造で列の値を根から引き継がせる

id  parent_id  auther
--  ---------  ---------
1   null       foo
2   1          foo
3   2          bar

3のようなレコードをDB制約で弾きたい。1つの木に auther が異なる枝が混入しないようにしたい。

環境

• SQLite3

元の状態

CREATE TABLE tree(
    id INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
    parent_id INTEGER,
    auther,
    FOREIGN KEY (parent_id) REFERENCES tree(id)
);

たどり着いたもの

CREATE TABLE tree(
    id INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
    parent_id INTEGER,
    auther,
    UNIQUE(id, auther),
    FOREIGN KEY (parent_id, auther) REFERENCES tree(id, auther)
);

ポイント

Auther フィールドを親から引き継がせるために、もとの隣接リストの foreign key 制約

FOREIGN KEY (parent_id) REFERENCES tree(id)

を、次のような複合 foreign key 制約に変える。

FOREIGN KEY (parent_id, auther) REFERENCES tree(id, auther)

これにより、自行の auther と参照先の auther が一致しない場合レコードは挿入できない。

Error: FOREIGN KEY constraint failed

なお、Foreign key 制約を課すためには参照先の列に Unique 制約が必要。

UNIQUE(id, auther)