特定の値が入ったときだけ、別のカラムに保存できるCHECK制約

CHECK制約は指定した条件がboolを返せば任意の式がかける。

Buildersconで話をしたけど下記のような場合は正規化が足りない。 その他カラムは好きなデータベースカラムの回答が その他 の場合のみ、任意の値を保存したい。

postgres=# SELECT * FROM enquete;

 id |  回答者  | 好きなデータベース   | その他
----+----------+--------------------+--------
  1 | soudai   | PostgreSQL         |
  2 | sone     | MySQL              |
  3 | taketomo | OracleDB           | SQLite ←不整合
  4 | test     | SQL Server         |
  5 | hoge     | その他             | Db2

上記の例では id=3 はその他列に不正な値が入っている。 これは正規化が足りてない証拠。 正規化するとこんな感じになる。

postgres=# SELECT * FROM enquete;

 id |  回答者  | 好きなデータベース
----+----------+-------------------
  1 | soudai   | PostgreSQL
  2 | sone     | MySQL
  3 | taketomo | OracleDB
  4 | test     | SQL Server
  5 | hoge     | Db2

postgres=# SELECT * FROM データベースの種類;

 id |   DBの種類 | 回答
----+------------+-------------------
  1 | PostgreSQL | PostgreSQL
  2 | MySQL      | MySQL
  3 | OracleDB   | OracleDB
  4 | SQL Server | SQL Server
  5 | DB2        | その他
  6 | SQLite     | その他

当たり前だがTABLEが増える。 そこでCHECK制約で非正規化しつつもデータを守ることができる。

postgres=# CREATE TABLE enquete (
id serial NOT NULL ,
"回答者" text NOT NULL,
"好きなデータベース" text NOT NULL,
"その他" text NOT NULL CHECK (
  CASE
      WHEN 好きなデータベース!='その他' AND その他='' THEN TRUE
      WHEN 好きなデータベース='その他' AND その他!='' THEN TRUE
      ELSE FALSE
  END)
);

CREATE TABLE
postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES
postgres-# (1, 'soudai', 'PostgreSQL', ''),(2, 'sone', 'MySQL', '');
INSERT 0 2

-- 好きなデータベースがその他の時以外はエラーになります
postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (3, 'taketomo', 'OracleDB', 'SQLite');
ERROR:  new row for relation "enquete" violates check constraint "enquete_check"
DETAIL:  Failing row contains (3, taketomo, OracleDB, SQLite).

-- 好きなデータベースがその他の時に空白でもエラーになります
postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (3, 'taketomo', 'その他', '');
ERROR:  new row for relation "enquete" violates check constraint "enquete_check"
DETAIL:  Failing row contains (3, taketomo, その他, ).

-- 正しくその他を登録する
postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (3, 'taketomo', 'その他', 'Db2');
INSERT 0 1

postgres=# SELECT * FROM enquete;
 id |  回答者  | 好きなデータベース | その他
----+----------+--------------------+--------
  1 | soudai   | PostgreSQL         |
  2 | sone     | MySQL              |
  3 | taketomo | その他             | Db2
(3 rows)

このようにCHECK制約の中でCASEが書ける。 ちなみにCASEは式なのでORDER BYの後ろなどどこでも書ける。

つまりCASEを使えばCHECK制約に任意の値を指定することもできるし、複雑なロジックも内包することができる。

しかし今回のシンプルな用途でも上記のようなCASE式になるためメンテナンス性は悪いからちゃんと正規化出来るならば、正規化したほうがいい。 あくまでも選択肢の一つとして覚えておくのが良い。 ちなみにCHECK制約はALTERで後から足せるのですでに有るTABLEに対しても有効である。

ちなみここまで書いて申し訳ないけどMySQLにはCHECK制約は無い。 CHECK制約を使いたい場合はPostgreSQLなどを使う必要がある。

※2019/01/24更新

bugs.mysql.com

Posted by developer:

Fixed in 8.0.15.

Previously, MySQL permitted a limited form of CHECK constraint syntax, but parsed and ignored it. MySQL now implements the core features of table and column CHECK constraints, for all storage engines. Constraints are defined using CREATE TABLE and ALTER TABLE statements.

ついに！！！MySQLに！！！CHECK制約が来るぞ！！！