Oracleって、可変長文字列型のフィールドに空文字列を設定（INSERT Or UPDATE）するとNullにする……というか、Nullと空文字列を区別しないのですね……。
今日、初めて意識しました。遅ればせながら……。
言い訳がましいけれど、ここ一年〜二年くらい、SQL ServerからOracleでの開発にシフトしたのに何故、今まで気が付かなかったかと言うと。
- 設計時に、文字列型フィールドでNullと空文字列を区別して格納する必要があるような設計をしていなかった。必要が無かった。
- ので、社内の既存テーブルで、過去の経緯からNullが設定されている*1文字列型フィールドを扱うケースが多かったので、アプリケーション側でデータ取得時に、一意に「DBNullだったら空文字列に変換する」関数に通していた。
- そもそもSQL Server出身者なので……＜これは単なる勉強不足
などなど……。
と言うか、設計に関しては、数値型ならまだしも、文字列型で「Nullに空文字列と違う意味を持たせる設計をする必要性」というのは、あまり思いつかないですが……*2。
今回何故、意識せざる得ない事態になったかと言うと……。
ある文字列型フィードに必ず値が入るものとし、他の２つのフィールドと合わせて３フィールドで一つのユニークキーを形成するようにテーブルを設計しました。
しかし、サンプルデータもらったら歯抜けになっているレコードがいくつか存在することが判明。
ただし、1.歯抜け自体はそんなに数が無いこと、2.歯抜けは必然的に歯抜けているのであって、それによって一意性が崩れるモノではない事が解ったので、設計はこのままでいけるかな、と踏みました。
まあ、ここまでは良かったのですが*3。
ところが、この歯抜けフィールドを含めたユニークキーは、他のテーブルと結合する際、他のテーブルに外部キー気味*4に使うよようにアプリを作っていました。
/* 概ね、こんな感じ。 */
/* 今回の問題のフィールドは、hoge_master_tableのhoge_sub_code。*/
select
t1.hogehoge_code ,
t2.hoge_name from
from
hoge_data_table t1,
hoge_master_table t2
where
t1.hoge_code = t2.hoge_code and
t1.hoge_sub_code = t2.hoge_sub_code and
t1.hoge_sub_sub_code = t2.hoge_sub_sub_code
ここで、アプリでこの歯抜けを含むデータを使ってデータを登録すると、結合したときにデータが取得できない。で、良く見ると、件のフィールドにNullが入っている。結合条件にNullが入っていれば当然結合できないので、データが取得できないのは当たり前です。
ですので、ここで私は、掲題の件をしらなかっがたために、安易に『んじゃ、空文字列を入れておけばええやん。』とか思って、おもむろにUPDATE文を走らせたわけです。
/* ふん、こんなもん、空文字列にしてしまえばええねん！！ */
update
hoge_master_table
set
hoge_sub_code = ''
where
hoge_sub_code is null
みたいな。
updete文はすんなり通ったので、これでうまくいく……と思いきや、やっぱり取れない。で、色々調べてたり試行錯誤して、ようやく真相に行き当たりました*5。
知ったときは、結構カルチャーショックだったり……。
ちなみに今回、上記の件をどうやって解決したかと言うと、テーブルの設計上は、別に主キーになるものを用意するようにして、例のフィールドを結合のキーに使うようにするのをやめるべきなんでしょうが、テーブルのメンテナンス方法などの絡みで、それは出来れば回避したい。また、Nullになっている
悩んでいたところ、先輩が意外な（？）解決を提案してくれました。
ある意味、目からウロコでした……。（今回の用途なら、パフォーマンス的にも問題なく対応可能っぽい）
/* レコード数にもよるけど、インデックスとか適切に効いてるならこれもアリじゃね？ */
/* 明示的に半角空白がセットされることが無いという前提ありき、だけれども。（by 先輩） */select
t1.hogehoge_code ,
2.hoge_name from
from
hoge_data_table t1,
hoge_master_table t2
where
t1.hoge_code = t2.hoge_code and
nvl(t1.hoge_sub_code,' ') = nvl(t2.hoge_sub_code,' ') and
t1.hoge_sub_sub_code = t2.hoge_sub_sub_code
