PostgreSQLでテーブルの継承

PostgreSQLには、テーブルの継承なんていう、とんがった機能があります。前衛的〜。

スキーマを定義してみる

-- オブジェクトテーブル
CREATE TABLE "object" (
	"object_id" SERIAL PRIMARY KEY,
	"name" VARCHAR(32) NOT NULL
);
--NOTICE:  CREATE TABLE will create implicit sequence "object_object_id_seq" for serial column "object.object_id"
--NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "object_pkey" for table "object"
--CREATE TABLE

-- ヒトテーブル
CREATE TABLE "human" (
	"height" INT,
	"weight" INT,
	PRIMARY KEY ("object_id")
) INHERITS ("object");
--NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "human_pkey" for table "human"
--CREATE TABLE

-- 製品テーブル
CREATE TABLE "product" (
	"price" INT,
	PRIMARY KEY ("object_id")
) INHERITS ("object");
--NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "product_pkey" for table "product"
--CREATE TABLE

継承元のCHECK制約, NOT NULL制約, DEFAULT値は引き継がれますが、UNIQUE制約, FOREIGN KEY制約, PRIMARY KEY制約は継承されないので、継承先であらためて指定する必要があります。

データを投入してみる

INSERT INTO "human" ("name", "height", "weight") VALUES ('山田太郎', 170, 65);
INSERT INTO "human" ("name", "height", "weight") VALUES ('田中次郎', 172, 70);
INSERT INTO "human" ("name", "height", "weight") VALUES ('佐藤花子', 159, 50);
INSERT INTO "product" ("name", "price") VALUES ('ホットコーヒー', 300);
INSERT INTO "product" ("name", "price") VALUES ('アイスコーヒー', 350);
INSERT INTO "product" ("name", "price") VALUES ('カフェオレ', 400);
INSERT INTO "product" ("name", "price") VALUES ('紅茶', 280);
INSERT INTO "product" ("name", "price") VALUES ('アイスティ', 330);
INSERT INTO "product" ("name", "price") VALUES ('ココア', 350);

そしてSELECTしてみる

# SELECT * FROM "human" ORDER BY "object_id";
 object_id |   name   | height | weight
-----------+----------+--------+--------
         1 | 山田太郎 |    170 |     65
         2 | 田中次郎 |    172 |     70
         3 | 佐藤花子 |    159 |     50
(3 rows)

# SELECT * FROM "product" ORDER BY "object_id";
 object_id |      name      | price
-----------+----------------+-------
         4 | ホットコーヒー |   300
         5 | アイスコーヒー |   350
         6 | カフェオレ     |   400
         7 | 紅茶           |   280
         8 | アイスティ     |   330
         9 | ココア         |   350
(6 rows)

# SELECT * FROM "object" ORDER BY "object_id";
 object_id |      name
-----------+----------------
         1 | 山田太郎
         2 | 田中次郎
         3 | 佐藤花子
         4 | ホットコーヒー
         5 | アイスコーヒー
         6 | カフェオレ
         7 | 紅茶
         8 | アイスティ
         9 | ココア
(9 rows)

他のRDBMSとの互換性は皆無ですが、上手く使えばとっても便利ですね。

これはできない？

ここで例えば、各object（ヒトと製品両方）のwebページが有るとして。そのページの閲覧ログをとりたいとします。
そこで、objectテーブルのobject_idを外部キーに指定して、こんな感じのテーブルを作ってみます。

-- 閲覧ログテーブル
CREATE TABLE "view_log" (
	"log_id" SERIAL PRIMARY KEY,
	"view_time" TIMESTAMP DEFAULT NOW(),
	"object_id" INT,
	FOREIGN KEY ("object_id")
		REFERENCES "object" ("object_id")
);
--NOTICE:  CREATE TABLE will create implicit sequence "view_log_log_id_seq" for serial column "view_log.log_id"
--NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "view_log_pkey" for table "view_log"
--CREATE TABLE

そこにデータ投入…。

-- 副問い合わせで、humanからobject_idを拾ってみる。
INSERT INTO "view_log" ("object_id")
	VALUES ((SELECT "object_id" FROM "human" WHERE "name" = '佐藤花子'));
--ERROR:  insert or update on table "view_log" violates foreign key constraint "view_log_object_id_fkey"
--DETAIL:  Key (object_id)=(3) is not present in table "object".

-- 副問い合わせで、objectからobject_idを拾ってみる。
INSERT INTO "view_log" ("object_id")
	VALUES ((SELECT "object_id" FROM "object" WHERE "name" = '佐藤花子'));
--ERROR:  insert or update on table "view_log" violates foreign key constraint "view_log_object_id_fkey"
--DETAIL:  Key (object_id)=(3) is not present in table "object".

-- んじゃ、ダイレクトにobject_idを投入してみる…。
INSERT INTO "view_log" ("object_id")
	VALUES (5); /* アイスコーヒーのobject_id */
--ERROR:  insert or update on table "view_log" violates foreign key constraint "view_log_object_id_fkey"
--DETAIL:  Key (object_id)=(5) is not present in table "object".

んーーー。SELECTしたら有るんだから良いじゃないですか…。objectテーブルからは見えるだけで、実体があるのはhumanテーブルやproductテーブルだから、無いんだ、って理屈なんでしょうけど…。

外部キーを外してしまうしか無いのでしょうか。