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

外部キーを設定しようとしましたが、日本医師会の併用禁忌データベースの情報に、現在の厚労省マスタに無い薬剤コードが含まれている事がわかり、そのためにうまく設定ができませんでした。
TBL_SSKIJYOおよびTBL_INTERACT間の外部キー参照は問題なさそうでしたので、TBL_INTERACT.DRUGCD2からTBL_YAKUJYO.YAKUHINCDへの外部キー参照のみを削除して、とりあえずすべての情報をPostgreSQLに流し込みました。
また、せっかくデータベースを最初から作り直すので、文字コードも懸念も解決しておこうと、kinkiデータベースの文字コードをUTF8に設定、iconvをもちいて流し込むデータもShift_JISからUTF8に変換しました。

tmiura:~/kinki tmiura$ createdb -E UTF-8 kinki

注意：データベースの文字コードをUTF-8にすると、Shift_JISのままのデータを流し込もうとしてもエラーが出ます。iconvなんかを使ってUTF-8に変換してください。MacOSX Tigerにはiconvは標準で入っているようです。

では、実際にどのくらいのレコードが厚労省医薬品マスタにすでに無い薬品コードを使っているのでしょうか？

kinki=# SELECT COUNT(drugcd) FROM tbl_interact WHERE NOT drugcd IN (SELECT yakuhincd FROM tbl_yakujyo);
count
-------
15022
(1 row)

kinki=# SELECT COUNT(drugcd2) FROM tbl_interact WHERE NOT drugcd2 IN (SELECT yakuhincd FROM tbl_yakujyo);
count
-------
16180
(1 row)

kinki=# SELECT COUNT(*) FROM tbl_interact;
count
-------
56040
(1 row)

psql83上で上記SQLを実行したところ、条件を満たすレコードは、全56040レコード中drugcdに15022、drugcd2に16180レコードが認められました。重なりがあるとはいえ、かなりの割合を占めています。ん〜、このデータ大丈夫かな？
次に、ここで見られた重なりのあるデータを削除していきます。

kinki=# DELETE FROM tbl_interact WHERE NOT drugcd IN (SELECT yakuhincd FROM tbl_yakujyo);
DELETE 15022
kinki=# DELETE FROM tbl_interact WHERE NOT drugcd2 IN (SELECT yakuhincd FROM tbl_yakujyo);
DELETE 11668
kinki=# SELECT COUNT(*) FROM tbl_interact;
count
-------
29350
(1 row)

とういことで、半分強にまで減ってしまいました。そして、外部キーを追加します。

kinki=# ALTER TABLE tbl_interact ADD FOREIGN KEY (drugcd2) REFERENCES tbl_yakujyo;
ALTER TABLE

これで、やっとデータベースの準備が完了しました。次回からは、いよいよNetBeans上でWebアプリケーションを作成していきたいと思います。