※今日までCHECK制約はCASE式も使えることを知らなかったので…メモします。
SQLの本やドキュメントのCHECK制約の説明は、制約の内容がとても単純なものになっていると思います。
例えば、PostgreSQLのドキュメントの場合、
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
のようにpriceが0を超える値だけ許可という単純なものです。
CKECKの()の中身はboolを返す式だったらOKですので、CASE式が使えます。
ErogameScapeではユーザーさんが任意のゲームについて、動作したOSを記載する項目があります。
OSを入力するとともに、1:動作、2:条件付きで動作、3:動作不可・インストール不可を選択します。
OSに「Wine on macOS」を追加することになったのですが、「Wine on macOS」は組み合わせがたくさんあるので、「2:条件付きで動作」のみ選択可として、memo列に組み合わせを書いて頂くことにしました。
「WIne on macOS」を選択した場合は「2:条件付きで動作」だけを許容したいです。
これをCHECK制約として追加すると以下のようになります。
ap2=# SELECT * FROM platformlist ;
id | name | sort | model
-----+------------------+------+-------
1 | Windows98 | 100 | PC
2 | Windows2000 | 200 | PC
3 | WindowsXP | 300 | PC
4 | WindowsVista | 400 | PC
5 | Windows7(32bit) | 500 | PC
6 | Windows7(64bit) | 600 | PC
7 | Windows8(32bit) | 700 | PC
8 | Windows8(64bit) | 800 | PC
164 | Windows10(32bit) | 900 | PC
165 | Windows10(64bit) | 1000 | PC
317 | Wine on macOS | 650 | PC
ap2=# \d support_platform
テーブル "public.support_platform"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
-----------+-----------------------------+----------+---------------+------------
uid | text | | not null |
game | integer | | not null |
platform | integer | | not null |
operation | integer | | not null |
memo | text | | |
timestamp | timestamp without time zone | | not null | now()
インデックス:
"support_platform_pkey" PRIMARY KEY, btree (uid, game, platform)
外部キー制約:
"support_platform_game_fkey" FOREIGN KEY (game) REFERENCES gamelist(id)
"support_platform_platform_fkey" FOREIGN KEY (platform) REFERENCES platformlist(id)
"support_platform_uid_fkey" FOREIGN KEY (uid) REFERENCES myuser(uid) ON UPDATE CASCADE ON DELETE CASCADE
凡例
operation
1 : 動作
2 : 条件付きで動作
3 : 動作不可・インストール不可
ap2=# ALTER TABLE support_platform
ADD CONSTRAINT support_platform_operation_check
CHECK (CASE
WHEN platform = 317
THEN operation = 2
END);
ap2=# INSERT INTO support_platform (uid,game,platform,operation) VALUES ('ap2',1,1,1);
INSERT 0 1
ap2=# INSERT INTO support_platform (uid,game,platform,operation) VALUES ('ap2',1,317,2);
INSERT 0 1
ap2=# INSERT INTO support_platform (uid,game,platform,operation) VALUES ('ap2',1,317,1);
ERROR: new row for relation "support_platform" violates check constraint "support_platform_operation_check"
DETAIL: Failing row contains (ap2, 1, 317, 1, null, 2019-09-30 19:45:35.259753).