データベースのセッティング(3) -外部キーの設定-

前回までで公開されているデータをローカルのPostgreSQLに流し込むところまではできました。では、いよいよNetBeans上での作業に移ろうかというところで、外部キーの設定を忘れていた事を思い出しました。というか、外部キーを適切に設定していないと、NetBeans上でデータベースを取り扱うのが煩雑になるようです。

まずは、どのようなインターフェースにするかですが、


だいたい上のようなイメージで考えています。

1. テキストボックスに商品名の一部を入力して、「検索」ボタンを押す。
2. 下のリストボックスに候補薬品一覧が表示される。
3. リストボックスにある候補薬品一覧の一つをクリックすると、併用禁忌になっている薬剤の一覧がしたの表に出力される。

1,2の部分は、tbl_yakujyoを検索し表示すれば良いので問題は少ないと思いますが、3の部分はリストボックスで選択された薬剤のyakuhincdを取得して、tbl_interactのdrugcdカラムを検索し、得られたレコードのdrugcd2およびsyojyoucdから、併用禁忌薬剤名、症状、作用機序の情報を得ます。ここで、外部キーの設定が必要になってきます。

ということで、外部キーの設定は、

1. TBL_INTERACT.DRUGCD2 → TBL_YAKUJYO.YAKUHINCD
2. TBL_INTERACT.SYOJYOUCD → TBL_SSKIJYO.SYOJYOUCD
   

となります。psql上でALTER TABLEを使っても良かったのですが、いったんテーブルを削除してあらたにCREATE TABLEからやり直す事にしました。以下に変更点を簡単にまとめます。

1. 外部キーで参照されるカラムは、プライマリキーである必要があるため、TBL_YAKUJYOテーブルおよびTBL_SSKIJYOテーブルのプライマリキーをYAKUHINCD、SYOJYOUCDにそれぞれ変更。TBL_YAKUJYO_ID、TBL_SSKIJYO_IDを廃止。
2. TBL_INTERACTテーブルを以下のように変更。
   

   CREATE TABLE TBL_INTERACT (
   TBL_INTERACT_ID SERIAL PRIMARY KEY,
   DRUGCD CHAR(9),
   DRUGCD2 CHAR(9) REFERENCES TBL_YAKUJYO,
   SYOJYOUCD CHAR(7) REFERENCES TBL_SSKIJYO
   );

今回は、すべてのSQLをkinki_db.sqlというファイルに書き込み、このファイルごとpsql83に流し込みました。

tmiura:~/kinki tmiura$ psql83 -d kinki -f kinki_db.sql
psql83:kinki_db.sql:36: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tbl_yakujyo_pkey" for table "tbl_yakujyo"
CREATE TABLE
psql83:kinki_db.sql:42: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tbl_sskijyo_pkey" for table "tbl_sskijyo"
CREATE TABLE
psql83:kinki_db.sql:49: NOTICE: CREATE TABLE will create implicit sequence "tbl_interact_tbl_interact_id_seq" for serial column "tbl_interact.tbl_interact_id"
psql83:kinki_db.sql:49: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tbl_interact_pkey" for table "tbl_interact"
CREATE TABLE
COPY 19747
COPY 992
psql83:kinki_db.sql:53: ERROR: insert or update on table "tbl_interact" violates foreign key constraint "tbl_interact_drugcd2_fkey"
DETAIL: Key (drugcd2)=(615101101) is not present in table "tbl_yakujyo".

なにやらエラーが出ました。ん〜。どうやら、外部キー制約が破られているらしい。tbl_interact.drugcd2の615101101というレコードがtbl_yakujyo.yakuhincdにないのが原因の様です。実際に両方のテーブルを検索してみて確認したところ間違いなさそうです。ちなみにググって見ると、どうやらいわゆる中止された薬剤のコードにあたるようですね。
ってことで、おそらく他にもたくさんこういったコードが紛れ込んでいる可能性があり、外部キーをつかうならばこの辺を解決しなければならないようです。

解決編は次回へ。