10章 31 Flavors(31のフレーバー)

少し変わった名前の章だが、これは本章の問題を、31アイスクリームの歴史をメタファーに、コラムで取り扱ったためである。バスキン・ロビンスに学べ、という事らしい。

##連絡先情報テーブルに敬称(salutation)を指定可能にする

予め定義した値だけを列に入力させたいケース、例えば連絡先情報テーブルに、決めた敬称だけを入力可能にする場合を想定してみる。

```lang:31-Flavors/intro/create-table.sql
CREATE TABLE PersonalContacts (
  name VARCHAR(20) NOT NULL,
  salutation VARCHAR(4) NOT NULL
    CHECK (salutation IN ('さん', '君', '様', '殿', '氏'))
);
```

値リストの指定は、「列のデータ型」としてや、「制約の宣言時」に行える。
これによって、無効な文字列の入力も防止出来る。

```lang:31-Flavors/intro/input-check-ok
mysql> INSERT INTO PersonalContacts (name, salutation) VALUES ("ゆきお", "様");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM PersonalContacts;
+-----------+------------+
| name      | salutation |
+-----------+------------+
| ゆきお    | 様         |
+-----------+------------+
1 row in set (0.00 sec)
```

問題無く INSERT できる。
次はCHECK制約にないテキストを入力してみる。

```lang:31-Flavors/intro/input-check-ng-1
mysql> INSERT INTO PersonalContacts (name, salutation)
    -> VALUES ("ゆきお", "ちゃん"); -- 「ちゃん」はエラーになるはず
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM PersonalContacts;
+-----------+------------+
| name      | salutation |
+-----------+------------+
| ゆきお    | 様         |
| ゆきお    | ちゃん     |
+-----------+------------+
2 rows in set (0.00 sec)
```

成功しちゃった！ ヽ（`Д´）ﾉ

*MySQLでは [Check制約が無視される](http://dev.mysql.com/doc/refman/5.6/en/create-table.html)ため、意味がないのであった。(2014年10月18日現在)*
>The CHECK clause is parsed but ignored by all storage engines.

ここでは無理矢理 trigger で制約を作ってみることにする。

```lang:31-Flavors/intro/input-check-trigger
mysql> delimiter $$
mysql> CREATE TRIGGER Insert_Personnel BEFORE INSERT ON PersonalContacts
    -> FOR EACH ROW
    -> BEGIN
    ->   IF (NEW.salutation NOT IN ('さん', '君', '様', '殿', '氏')) THEN
    ->     SET NEW.salutation = NULL;
    ->   ELSE
    ->     BEGIN END;
    ->   END IF;
    -> END;$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
```

予めテーブル作成時に NOT NULL 指定をしておいて、規定の敬称以外が来たら NULL に置き換えてエラーにするトリガーである。

```lang:31-Flavors/intro/input-check-ng-3
mysql> INSERT INTO PersonalContacts (name, salutation) VALUES ("ゆきお", "氏");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PersonalContacts (name, salutation) VALUES ("ゆきお", "ちゃま");
ERROR 1048 (23000): Column 'salutation' cannot be null
mysql> SELECT * FROM PersonalContacts;
+-----------+------------+
| name      | salutation |
+-----------+------------+
| ゆきお    | 様         |
| ゆきお    | ちゃん     |
| ゆきお    | 氏         |
+-----------+------------+
3 rows in set (0.00 sec)
```

無事、ゆきおちゃまをエラーに出来たようである。
しかし、これだけだと INSERT 時にしか対応出来ていないので、別途 UPDATE 用の trigger も用意しなければいけないし、おまけに、敬称の仕様が変わればロジックの修正が必要になり……頭の痛い問題である。


## 10.1 目的：列を特定の値に限定する

列に格納できる値を限定出来ると、無効な値が含まれていない事を保証でき、列をシンプルに使える。
例えば、この本のサンプルとして登場する Bugsテーブル内の status列で、バグの状態を

* NEW
* IN PROGRESS
* FIXED


などで指定しているとすると、status列には必ず、このどれかの値が格納されているという事。
データベースが無効なデータ入力を受け付けないのが理想なのである。

## 10.2 アンチパターン：限定する値を列定義で指定する

そのためにすぐに思いつく解決策は、有効なデータ値を列の定義時に指定するというもの。
前段では CHECK制約や trigger を試したので、今度は ENUM で指定してみる。

```lang:31-Flavors/obj/create-table-enum.sql
CREATE TABLE Bugs (
  -- 他の列...
  status ENUM('NEW', 'IN PROGRESS', 'FIXED')
);
```

だが、ENUMは内部的には文字列として格納されておらず、ソート結果は列での値の定義順になってしまう。

```lang:31-Flavors/obj/create-table-enum-check
mysql> INSERT INTO Bugs (status) VALUES ('NEW'); -- OK
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('IN PROGRESS'); -- OK
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('FIXED'); -- OK
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('BANANA'); -- エラー！
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT status FROM Bugs ORDER BY status;
+-------------+
| status      |
+-------------+
|             |
| NEW         |
| IN PROGRESS |
| FIXED       |
+-------------+
4 rows in set (0.00 sec)
```

ENUM の定義に無い値は NULL になったが、SELECT での結果のソート順序は分かりにくい。

他の解決策には、ドメインやユーザー定義型 (UDT)を利用したりする方法も考えられるが、これらには、そもそもの欠点が存在するのであった。

#### 10.2.1 中身は何だろう

Bugs テーブル内の ENUM で定義された status 値を、使用可能な値として抽出しようとした場合...

```lang:31-Flavors/anti/information-schema-ex
mysql> SELECT column_type FROM information_schema.columns WHERE table_name = 'Bugs' AND column_name = 'status';
+-----------------------------------+
| column_type                       |
+-----------------------------------+
| enum('NEW','IN PROGRESS','FIXED') |
+-----------------------------------+
1 row in set (0.00 sec)
```

この文字列をパースして、各値を抽出して...ってオイ。抽出するクエリも結果も複雑。

ちなみに...
最初の例で作った trigger に至っては、SHOW TRIGGERS でしか抽出出来ず、更に悲惨。

```lang:31-Flavors/anti/information-schema-ex2
mysql> SHOW TRIGGERS LIKE 'PersonalContacts'\G
*************************** 1. row ***************************
             Trigger: Insert_Personnel
               Event: INSERT
               Table: PersonalContacts
           Statement: BEGIN
  IF (NEW.salutation NOT IN ('さん', '君', '様', '殿', '氏')) THEN
    SET NEW.salutation = NULL;
  ELSE
    BEGIN END;
  END IF;
END
              Timing: BEFORE
             Created: NULL
            sql_mode:
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
```

*結論: 入力可能な値の抽出が辛い*

#### 10.2.2 新しいフレーバーの追加

Bugs テーブル内の ENUM で定義された status 値に、DUPLICATE を追加したい場合...

```lang:31-Flavors/anti/add-enum-value
mysql> ALTER TABLE Bugs MODIFY COLUMN status ENUM('NEW', 'IN PROGRESS', 'FIXED', 'DUPLICATE');
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE Bugs;
+--------+-----------------------------------------------+------+-----+---------+-------+
| Field  | Type                                          | Null | Key | Default | Extra |
+--------+-----------------------------------------------+------+-----+---------+-------+
| status | enum('NEW','IN PROGRESS','FIXED','DUPLICATE') | YES  |     | NULL    |       |
+--------+-----------------------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
```

列定義を変えようと思ったら ALTER TABLE するしかない。

[参考: ALTER TABLE実行する時は注意しましょう](http://qiita.com/cs_sonar/items/0d4dc4477d0fea01cb09)

*結論: DBのメンテナンス時間が必要になるし、変更のコストが高い*

#### 10.2.3 昔ながらの味は色褪せない

Bugs テーブル内の status 値から、FIXED を CODE COMPLETE と VERIFIED に分けたい場合...

```lang:31-Flavors/anti/remove-enum-value
mysql> ALTER TABLE Bugs MODIFY COLUMN status ENUM('NEW', 'IN PROGRESS', 'CODE COMPLETE', 'VERIFIED');
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> SELECT status FROM Bugs ORDER BY status;
+-------------+
| status      |
+-------------+
|             |
|             |
| NEW         |
| IN PROGRESS |
+-------------+
4 rows in set (0.00 sec)
```

元々 FIXED だったステータス値は、そのままやってしまうと NULL になってしまう。
そして、入力は制限したいが、過去の status 値はそのまま残しておきたいケースでは、前述の ENUM 定義の取得だけでは、どれが使ってよい有効な値なのか分からなくなってしまう。

*結論: 値の廃止に柔軟性が無い*

#### 10.2.4 移植が困難

```
+----------------------+--------+--------+--------+---------------------+
| DBMS                 | ENUM   | CHECK  | DOMAIN | UDT サポート         |
+----------------------+--------+--------+--------+---------------------+
| ANSI SQL標準         | なし    | SQL89  | SQL92  | SQL99               |
+----------------------+--------+--------+--------+---------------------+
| MySQL                | はい    | いいえ | いいえ  | いいえ              |
+----------------------+--------+--------+--------+---------------------+
| IBM DB2®             | いいえ  | はい   | いいえ  | はい (単純および複雑)|
+----------------------+--------+--------+--------+---------------------+
| Microsoft SQL Server | いいえ  | はい   | いいえ  | はい (単純および複雑)|
+----------------------+--------+--------+--------+---------------------+
| Oracle               | いいえ  | はい   | いいえ  | はい (複雑)         |
+----------------------+--------+--------+--------+---------------------+
| PostgreSQL           | いいえ  | はい   | はい    | はい                |
+----------------------+--------+--------+---------+--------------------+
```
[参照元: データ型サポート] (http://www-01.ibm.com/support/knowledgecenter/SSBLQQ_8.5.1/com.ibm.rational.rtvs.ref.doc/topics/c_rtvs_db_stubs_data_type_support.html?cp=SSBLQQ_8.5.1%2F1-6-6-3-1-4-0-3&lang=ja)

*結論: これらの方法が、他の RDBMS でも使えるとは限らない*

## 10.3 アンチパターンの見つけ方

ENUM や CHECK制約を使っていて問題に遭遇するのは、値が変わる可能性がある場合である。
アンチパターンの利用を示唆しているのは...

* メニュー追加のような変更のために、DBをオフラインにしようとしている
* status列は、この値のうち1つを持つ事が出来る。リストの修正が必要になることは、まずないはずだ
* アプリケーションコード側の値リストが、ビジネスルールと同期していない

## 10.4 アンチパターンを用いても良い場合

値セットが変わらないと断言できるような場合。
特に、ENUM に最適なのは、相互排他的な2つの値を指定するような場合。

* 左/右
* 有効/無効
* オン/オフ
* 内部/外部

## 10.5 解決策：限定する値をデータで指定する

status が持ちうる値を持った参照テーブルを作成し、Bugs.status に外部キー制約を宣言して参照させる。

```lang:31-Flavors/soln/create-lookup-table
mysql> DROP TABLE Bugs;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE BugStatus (
    -> status VARCHAR(20) PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE Bugs (
    -> -- 他の列...
    -> status VARCHAR(20),
    -> FOREIGN KEY (status) REFERENCES BugStatus(status)
    ->   ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT status FROM BugStatus;
+-------------+
| status      |
+-------------+
| FIXED       |
| IN PROGRESS |
| NEW         |
+-------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('NEW'); -- OK
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('BANANA'); -- エラー！
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dev`.`Bugs`, CONSTRAINT `Bugs_ibfk_1` FOREIGN KEY (`status`) REFERENCES `BugStatus` (`status`) ON UPDATE CASCADE)
mysql> SELECT status FROM Bugs;
+--------+
| status |
+--------+
| NEW    |
+--------+
1 row in set (0.00 sec)
```

#### 10.5.1 値セットの取得

楽だね。ソート順序もアルファベット順。

```lang:31-Flavors/soln/query-canonical-values
mysql>  SELECT status FROM BugStatus ORDER BY status;
+-------------+
| status      |
+-------------+
| FIXED       |
| IN PROGRESS |
| NEW         |
+-------------+
3 rows in set (0.00 sec)
```

#### 10.5.2 参照テーブルの値の更新

瞬殺。

```lang:31-Floavors/soln/insert-value
mysql> INSERT INTO BugStatus (status) VALUES ('DUPLICATE');
Query OK, 1 row affected (0.00 sec)
```

ON UPDATE CASCADEを使っていれば、 値の Rename も楽々。

#### 10.5.3 廃止された値のサポート

BugsStatus に、その値が ACTIVE か INACTIVE かを保持するようにすれば解決。

```lang:31-Floavors/soln/inactive
mysql> ALTER TABLE BugStatus ADD COLUMN active
    ->   ENUM('INACTIVE', 'ACTIVE') NOT NULL DEFAULT 'ACTIVE';
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESCRIBE BugStatus;
+--------+---------------------------+------+-----+---------+-------+
| Field  | Type                      | Null | Key | Default | Extra |
+--------+---------------------------+------+-----+---------+-------+
| status | varchar(20)               | NO   | PRI | NULL    |       |
| active | enum('INACTIVE','ACTIVE') | NO   |     | ACTIVE  |       |
+--------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```

値を廃止するときは、DELETE ではなく UPDATE で INACTIVE にする。

```
mysql> UPDATE BugStatus SET active = 'INACTIVE' WHERE status = 'DUPLICATE';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT status FROM BugStatus WHERE active = 'ACTIVE';
+-------------+
| status      |
+-------------+
| FIXED       |
| IN PROGRESS |
| NEW         |
+-------------+
3 rows in set (0.00 sec)

mysql> SELECT status FROM BugStatus WHERE active = 'INACTIVE';
+-----------+
| status    |
+-----------+
| DUPLICATE |
+-----------+
1 row in set (0.00 sec)
```

これで過去に使っていて廃止された値だけを除外するのも楽々。

#### 10.5.4 移植が容易

MySQLしか使えない、MySQLでは使えない...といった事がない。

## まとめ

列に入力する値を限定するときは、値セットが固定されている場合はメタデータを、流動的な場合はデータを用いましょう。