はじめに
本記事はPostgreSQL Advent Calendar 2020の13日目です。
ここではINHERITS句を用いたテーブル継承について書きます。
ドキュメントをきちんと読まずに軽い気持ちでINHERITS句を指定したところ、後に自分が盛大な勘違いをしていたことが発覚し大やけどした経験を共有したいと思います。今後同じ思いをされる方が一人でも減りますように。。
結論
INHERITS句を使う前に確認しておきたいことは、２点に尽きます。
- 子テーブルへ挿入されたデータは自動的に親テーブルにも挿入されるわけではない（逆もしかり）
- 親テーブルの主キー制約（や一意性制約）は子テーブルに継承されない
いずれも参考にあるようなドキュメントに５分も目を通せば明らかなことなのですが、一度間違った解釈をしてしまうと正しい理解に戻ってくるまでに時間を要しました。
実行環境はDocker公式イメージpostgres:13.1を使用しています。
子テーブルへ挿入されたデータは親テーブルにも挿入されるわけではない
オブジェクト指向のクラス継承のように、INHERITS句を使うと親テーブルが持つカラムを子テーブルに継承することができます。
CREATE TABLE company (
security_code char(4) PRIMARY KEY,
name varchar(256)
);
CREATE TABLE axx_company (
name_ja varchar(256),
address varchar(256)
) INHERITS (company);
CREATE TABLE bxx_company (
isin_code char(12)
) INHERITS (company);
postgres=# \d axx_company
Table "public.axx_company"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
security_code | character(4) | | not null |
name | character varying(256) | | |
name_ja | character varying(256) | | |
address | character varying(256) | | |
Inherits: company
やりたいこととしては、データセットAxxとデータセットBxxからとってきた会社のデータをそれぞれaxx_companyテーブルとbxx_companyテーブルへ格納し、親のcompanyテーブルで全会社を登録されている状態を作るというものでした。実際にデータを挿入してみるとそのような見え方をします。
INSERT INTO axx_company VALUES ('1111', '1 Corp.', '株式会社１', '東京都新宿区１丁目');
INSERT INTO axx_company VALUES ('2222', '2 Corp.', '株式会社２', '東京都新宿区２丁目');
INSERT INTO bxx_company VALUES ('3333', '3 Inc.', 'JP0000033333');
postgres=# SELECT * FROM company;
security_code | name
---------------+---------
1111 | 1 Corp.
2222 | 2 Corp.
3333 | 3 Inc.
(3 rows)
ここで当時の私はとんだ勘違いをしました。「なるほど、子テーブルに挿入されたデータが射影されて親テーブルにも入るのか」と。。
・・なわけではありません。実際には、親テーブルの中は空っぽで、SELECT * FROM companyが下のクエリの要領で継承したテーブルたちのレコードを取得してきているということでした。
SELECT security_code, name FROM company
UNION
SELECT security_code, name FROM axx_company
UNION
SELECT security_code, name FROM bxx_company;
親テーブルへのSELECTは、透過的に子（孫）テーブルのレコードまで参照することができます。UPDATEやDELETEも、同様に親テーブルにかけた更新が子テーブルにまで及びます。しかしながらINSERTは、あくまでも指定したテーブルのみにレコードが挿入されます。子テーブルに挿入すれば自動的に親テーブルにも挿入される！というわけではありません。同様に親テーブルに挿入しても子テーブルには挿入されません。
また、下のようにusersテーブルがcompanyテーブルを外部キー参照しているとき、子のaxx_companyテーブルに存在する値でINSERTを試みても親のcompanyテーブルに存在しなければ参照制約エラーが返ってきます。
postgres=# INSERT INTO users VALUES (1, '名無し', '1111');
ERROR: insert or update on table "users" violates foreign key constraint "users_security_code_fkey"
DETAIL: Key (security_code)=(1111) is not present in table "company".
親テーブルの主キー制約は子テーブルに継承されない
親テーブルで宣言した主キー制約は子テーブルに継承されません。同様に一意性制約や外部キー制約も継承されません。親テーブルの主キー制約は子テーブルには非NULL制約のみが引き継がれます。なのでALTER TABLE bxx_companyして主キー制約を別途つけてあげなければ、下のような現象も容易に起こりえます。
postgres=# SELECT * FROM bxx_company;
security_code | name | isin_code
---------------+--------+--------------
3333 | 3 Inc. | JP0000033333
3333 | 3 Inc. | JP0000033333
さらに注意すべきは親のcompanyテーブルの主キー制約です。
postgres=# SELECT * FROM company;
security_code | name
---------------+---------
1111 | 1 Corp.
2222 | 2 Corp.
3333 | 3 Inc.
3333 | 3 Inc.
(4 rows)
company.security_codeには一意性制約があるので一見これはあり得ないように見えますが、あくまでもcompanyテーブルの主キー制約はcompanyテーブル自身のみにかかるものなので、子テーブル内（もしくは子テーブル間）で重複しようとも親テーブルはお構いなしです。
伝統的なテーブル継承
オブジェクト指向の継承関係をINHERITS句を用いたテーブル継承で実装してみようとすると、専らサブタイプへデータを挿入したい場合には使い勝手があまり良くありません。
リレーショナルデータベースでテーブル継承を表現する伝統的な方法として、PofEAAや『SQLアンチパターン』で紹介されているものは、シングルテーブル継承・具象テーブル継承・クラステーブル継承と呼ばれるものです2。
使用するフレームワークやO/Rマッパーのライブラリによってどの方式をとるかは変わってくると思いますが、一例としてクラステーブル継承のかたちを取ると今回の例では下のようなER図になります。
サブタイプごとに１つずつテーブルを持ち、スーパータイプのテーブルに対する外部キーの役割を果たす主キーを設定します。利点としてはcompanyテーブルに全会社のデータが入ることが保証されるので、companyテーブルと他のテーブルを関連させたいというときにも効率よく実現することができます。
まとめ
PostgreSQLのINHERITS句に関して犯した勘違いについて書いてみました。
この記事を書きながら、冷静に考えてみたら色々つじつまが合わないのに当時の自分はなんでそんな不思議な解釈をしてしまったのかと情けなく思いました。
飛びつく前にきちんと調べないと・・
ただ一通り調べてみると、結局このINHERITS句を使うテーブル継承のありがたみがいまひとつ実感できませんでした。どういった場合に真価を発揮してくれるのでしょうか？
参考
本記事はINHERITS句の機能の一部にしか焦点を当てていません。実際の仕様の全貌については、以下を参考にしてください。
- [公式] PostgreSQL: Documentation: 13: 5.10. Inheritance
- [DBOnline] テーブルを継承して新しいテーブルを作成する(CREATE TABLE ... INHERITS)
明日は@yugo-nさんです！
-
security_codeは証券（銘柄）コード、isin_codeは国際証券識別番号を表しています。 ↩