はじめに
本エントリーは某社内で実施するSQLアンチパターン勉強会向けの資料となります。
本エントリーで書籍「SQL アンチパターン」をベースに学習を進めます。書籍上でのサンプルコードはMySQLですが、本エントリーでのサンプルコードはT-SQLに置き換えて解説します。
サーティーワンフレーバー とは
列に特定の値を限定したいときに、限定する値を列定義で指定してしまったときに発生するアンチパターン。
「特定の値」が追加・変更されることを考慮していないため、変更の際により労力を要する。
具体例
Bugsテーブルに、バグの状態を管理するstatus列を定義することを例とする。
この時、check制約により既定の値のみ入力できるようにするには、以下のような列定義となる。
CREATE TABLE Bugs(
bug_id int identity primary key,
date_reported datetime2 not null,
summary varchar(80),
description varchar(1000),
resolution varchar(1000),
reported_by int not null,
assigned_to int,
veryfied_by int,
-- status列にcheck制約をかけ、3種類の値のみ登録可能とする
status varchar(20) not null CHECK(status IN ('NEW','IN PROGRESS','FIXED')),
priority varchar(20),
hours decimal(9,2)
)
--ENUMについては、T-SQLでは利用できないので割愛
上記のようなテーブル構造をとった場合、以下のような場面で問題が発生する場合がある
- とりうる値のリストの取得
- とりうる値の追加
- とりうる値の削除
- 他DB製品への移植
問題点1:列のとりうる値のリストの取得
status列のとりうる値は、列の制約という形でしか定義されていない。
そのため、とりうる値の一覧を取得したい場合、システムビューを参照する必要がある。
SELECT
name,
type_desc,
definition
FROM
sys.check_constraints
このクエリにより、以下のような結果こそ得られるが、この結果から入力可能な値のリストに変形するのは労力が要るだろう。
| name | type_desc | definition |
|---|---|---|
| CK_Bugs_status_3D5E1FD2 | CHECK_CONSTRAINT | ([status]='FIXED' OR [status]='IN PROGRESS' OR [status]='NEW' OR [status]='DUPLICATE') |
問題点2:とりうる値の追加
しかし、ENUMの値やCHECK制約を追加または削除するための構文は無く、新たな値セットで列を再定義するしか方法はありません。
とあるが、MySQLの最新版ではCHECK制約のDROP、ADDに対応している様子。
また、T-SQLでは当然CHECK制約の追加・削除をサポートしている。
-- 一度CHECK制約を削除し、新たに定義した制約にとりうる値を追加する
ALTER TABLE Bugs DROP CONSTRAINT CK__Bugs__status__3C69FB99
GO
ALTER TABLE Bugs WITH CHECK ADD CHECK (([status]='FIXED' OR [status]='IN PROGRESS' OR [status]='NEW' OR [status]='DUPLICATE'))
GO
とはいえ、テーブル定義の変更はあまり頻繁に行いたいものではない。
問題点3:とりうる値の削除
例えば、テーブルの値が以下の時に、上述のCHECK制約の削除・追加を行うとする。
| bug_id | status |
|---|---|
| 1 | VERIFIED |
すると実行時エラーとなり、以下のようなエラー文が出力される。
メッセージ 547、レベル 16、状態 0、行 7
ALTER TABLE ステートメントは CHECK 制約 "CK__Bugs__status__3E52440B" と競合しています。競合が発生したのは、データベース "SqlAntiPattern"、テーブル "dbo.Bugs", column 'status' です。
CHECK制約によりデータの入力制御を行っていた場合、既存の値を廃止したい場合に問題が発生する場合がある。
（尤も、後述の方法でもこの問題は発生しうるが。）
問題点4:他DB製品への移植
MySQLのENUMのような、他製品ではサポートされていない定義方法を利用していた場合、移行の際に定義変更にコストが必要になるだろう
アンチパターンの見つけ方
列に特定の値を限定したいとき、このアンチパターンを用いてしまうことが多い。
列の入力値に定義から制限をかけたい場合、「その値リストは本当に不変か？」を考える必要があるだろう。
アンチパターンを用いてもよい場合
列の値が不変であることが保証されている場合は、定義により入力値を制限してもよいだろう。
右 or 左のような、相互排他的な2つの値はこれに該当すると考えられる。
解決策：とりうる値を他のテーブルに登録する
上記の例であれば、Bugsテーブルのstatus列がとりうる値をBugStatusテーブルに切り分け、BugsテーブルからBugStatusテーブルへの外部キーを設定すればよい。
CREATE TABLE Bugs(
bug_id int identity primary key,
date_reported datetime2 not null,
summary varchar(80),
description varchar(1000),
resolution varchar(1000),
reported_by int not null,
assigned_to int,
veryfied_by int,
status varchar(20) not null,
priority varchar(20),
hours decimal(9,2),
FOREIGN KEY (status) REFERENCES BugStatus(status)
)
create table BugStatus(status varchar(20) primary key)
列のとりうる値のリストの取得
列のとりうる値がBugStatusテーブルに定義されているため、以下のクエリで簡単に取得できる。
SELECT status FROM BugStatus
とりうる値の追加
BugStatusテーブルに追加したい値をInsertすればよい。
INSERT INTO BugStatus VALUES 'DUPLICATE'
-- FOREIGN KEYにON UPDATE CASCADEを設定しておけば、既存の値の変更も行える
INSERT INTO BugStatus VALUES 'INVALID' WHERE status = 'BOGUS'
とりうる値の削除
外部キー制約により入力値を強制する場合でも、既存の値を削除しようとすると実行時エラーとなる。
但し、BugStatusテーブルに「ステータスが現在利用されているか」を管理する列を追加することで、
「廃止された既存の値」と「現在システムで利用している値」を区別できる点は、CHECK制約の利用と比べ差別化できるといえる。
他DB製品への移植
外部キー制約は標準的なSQL機能であるので、製品に依存する度合いが少なく、移行時のトラブルになりにくいと考えられる。
（とはいえ、前述のとおりCHECK制約も標準機能に含まれつつあるので、利用する時々によって使い分けるとよいだろう。）