12
2008

一意でない列にUNIQUE制約を付ける(Oracle 10g)

CATEGORYDB
こんな感じのテーブル定義があったとする。
CREATE TABLE MEMBER (
ID NUMBER(6) NOT NULL,
MAIL_ADDRESS VARCHAR2(256) NOT NULL,
DELETE_FLAG NUMBER(1) DEFAULT 0 NOT NULL,
PRIMARY KEY(ID)
);

ご覧のように、主キーはID列。
他はNOT NULL制約だけが付いていて、特にこれといった制約は無し。

が、実際には、
1 test@sample.com 1
2 test@sample.com 1
3 test@sample.com 0
4 test2@sample.com 0

というようなデータしか入ってこず、実はMAIL_ADDRESS列はDELETE_FLAG列が0の値では一意だったりする。

なので、MAIL_ADDRESS列にも制約を付けたいと考えるわけだが、1列にしろ2列を組み合わせるにしろ、重複データ自体は許可されているため、ユニーク制約は適用できない。
普通は、どうしてもユニーク制約が適用したいならテーブル設計を見直し、できないなら自力でがんばってチェックということになる(と思う)。

・・・と行き詰っていたのだが、実はOracleではファンクション索引を用いることで、こういうデータにもユニーク制約を実現することができる。

CREATE UNIQUE INDEX MEMBER_MAIL_ADDRESS_UK ON MEMBER
(CASE WHEN DELETE_FLAG = 0 THEN MAIL_ADDRESS ELSE NULL END)

こんな風に UNIQUE を指定したファンクション索引を作ることで、制約と同様の効果が得られる。
(いらないデータはユニーク制約では無視されるNULLに置き換え。)
すばらしいことに、INSERT時の重複チェックや他のトランザクションとの排他・ロック待ちなんかも、普通のユニーク制約と同様に行われる。
自力でチェック処理を作るのと比べたら雲泥の差がある。

唯一違う点といえば、これは(当然ながら)ファンクション索引なので、MAIL_ADDRESS列の普通のインデックスとしては利用できないこと。
とはいえ、別途もう一つ検索用のインデックスを付ければいくらでも回避できる。


はじめからもっと考えてテーブル設計をするのがベストだろうが、世の中はそうはいかなかったり、既存のどうしようもないものを渡されることも多々ある(--;
そういうときに、この方法はかなり便利ではと思う。

# とここまで書いたけど、これ実際の開発では試してない(他に事情があって没、、、)ので、本格的に使ったら何か穴があるかも(おぃ


2008/12/20 体裁修正、リンク追加
スポンサーサイト

Tag: Oracle

0 Comments

Leave a comment