- PR -

PostgreSQL 8.0.2 (on WinXP) で主キーを日本語にした場合の検索

1
投稿者投稿内容
ロス
常連さん
会議室デビュー日: 2005/03/25
投稿数: 26
投稿日時: 2005-04-20 16:18
主キーが日本語のときに、"="で検索ができず、そのため参照制約違反が出て
困っています。
どなたか、解決方法をご存知でしたらアドバイスお願いします。

以下、今までの経緯です。。。

■問題発生契機
PostgreSQLを利用した開発（win2000）を行い、その環境をwinXP（ノートPC）へ移行しようとしていた段階で、データ投入時に外部キー制約違反が発生しました。
使用データベースはPostgreSQL　8.0.2です。

■解析情報その１（テーブル構成）

・被参照テーブル（マスタテーブル）
例）reague
type（主キー）
---------------
セリーグ
パリーグ

・参照テーブル
例）team
team_id（主キー）|reague_type（外部キー）|team_name　　| ・・・
-------------------------------------------------------------
0001　　　　　　　|セリーグ　　　　　　　|ベイスターズ| ・・・

上記のようなテーブル構成で、リーグ表をInsertしたあと、
チーム表をInsertしようとしたときに、外部キー制約に違反していますと
ERRORがでます。
*************************************
ERROR: insert or update on table "team" violates foreign key constraint "外部キー名"
DETAIL: Key (type) = (セリーグ) is not present in table "reague"
*************************************

■解析情報その２（SQLでの確認）
pgAdmin3ツールを利用して検証をしてみました。

select type from reague;
で、全件表示されます。
select type from reague where type = "セリーグ";
は一件も表示されません。
select type from reague where type like "セリーグ";
ですと、一件（セリーグのもの）が表示されます。

■解析情報その３（主キーではない場合）
team_name = "ベイスターズ"は検索にヒットしました。
# "=" でも "like" でも期待通りに文字列比較が行われています。

■解析情報その４（元の環境）
通常動作しています（上記のようなことは起こっていません）。

■解析情報その５（別のPC）
ノートPCではなく、同じ型のマシンで、しかもWindowsXPに
DB（PostgreSQL 8.0.2）をインストールさせていただき、
検証を行いましたが、同様の不具合が発生しています。

それとは別に、ノートPCのWindows2000で試しましたが、
これも同様の不具合が発生しています。
ロス
常連さん
会議室デビュー日: 2005/03/25
投稿数: 26
投稿日時: 2005-04-20 16:28
すみません、環境にモレがありました。

・テーブルのエンコードはEUC-JPです。
・データベースは、全ての環境で
C:\Program Files\PostgreSQL\8.0
　以下にインストールされています。

また、CSEでinsertを行っても同じ結果になりました。。。
（当たり前ですが・・・）
今川 美保(夏椰)
ぬし
会議室デビュー日: 2004/06/10
投稿数: 363
お住まい・勤務地: 神奈川県茅ヶ崎市
投稿日時: 2005-04-20 18:23
解決方法ではないのですが、
自PC(Win2000)にPostgreSQL8.0をいれて実験してみました。

(前提)
１）キーを持つ列をchar(20)で定義、主キーインデクス作成。
２）レコードを'あ'を3個のものと、'い'を20個のレコード(計2レコード)を
INSERT文を発行して追加。

(実験)
１） キーに'あああ'を条件にしてEqual検索 ⇒Hitせず
２） キーに'い(×20個)を条件にしてEqual検索⇒Hitする
３） キーに'%あああ%'を条件にLike検索⇒Hitする
４） キーに'あああ%'を条件にLike検索⇒Hitする
５） キーに'%あああ'を条件にLike検索⇒Hitしない
６） キーに'あああ_(スペース17個)'を条件にEqual検索⇒Hitしない

(推測)
キーの長さが定義の列長に満たないマルチバイト文字は後ろに謎なコードが入っている？
って感じです(^^;

で、そもそも論で申し訳ないのですが、
なぜリーグ表はリーグ名を主キーにされたんですか？
一意になる番号なりを振ってリーグ表にデータを作成し、
その番号をチーム表の列として追加すれば
わざわざキーをマルチバイト文字にしなくて済むし、
この問題に遭遇しなかったのでは？


[ メッセージ編集済み 編集者: 夏椰 編集日時 2005-04-20 18:24 ]
ロス
常連さん
会議室デビュー日: 2005/03/25
投稿数: 26
投稿日時: 2005-04-21 10:01
夏椰さん、リプライありがとうございます。
引用:

２） キーに'い(×20個)を条件にしてEqual検索⇒Hitする
４） キーに'あああ%'を条件にLike検索⇒Hitする
(推測)
キーの長さが定義の列長に満たないマルチバイト文字は後ろに謎なコードが入っている？
って感じです(^^;

非常に興味深いです。
# 境界値試験をしていなかったのはごめんなさい。。。orz

文字列の後ろに何某かが入るように見えますね。。

引用:

で、そもそも論で申し訳ないのですが、
なぜリーグ表はリーグ名を主キーにされたんですか？
一意になる番号なりを振ってリーグ表にデータを作成し、
その番号をチーム表の列として追加すれば
わざわざキーをマルチバイト文字にしなくて済むし、
この問題に遭遇しなかったのでは？


データ設計を行った際に、２つのテーブルに分けたのは実際には２値ではなく３０値ほど（増加可能性あり）という条件のためでした。
しかし、その利用目的が参照制約を用いて値の制限をするだけにとどまっていたため、「リーグID」に相当するものを必要としないと考えたためです。

この件では、IDを振ることの大切さを学びました。
ロス
常連さん
会議室デビュー日: 2005/03/25
投稿数: 26
投稿日時: 2005-04-21 15:32
とりあえずの解決をしましたので、報告します。

そもそも、windows2000で動いている環境があるので動作するはずであるという
前提で、環境の相違を調べていきました。

# 環境の相違の視点でPostgreSQLのMLを探しました。。。

各PCで、「select * from pg_setting;」を実行したところ、

●不具合が起こらない環境
name |type
-----------------------------
lc_ctype |C
他・・・

●不具合が起こる環境
name |type
-----------------------------
lc_ctype |Japanese_Japan.932
他・・・

となっていました。
これは、initdb時に指定するlocateの設定で変化します。

そこで、ここを同じにしてみようと思い、データベースを初期化から再作成したところ、
lc_**** の値は C になり、期待通りの動作を行うようになりました。
# GUIからのデータベース初期化の時点で、Japaniseを選択せずにCを選択しました。


（そもそものテーブル設計がよくないという指摘を頂きましたが、）
一応のバックアップ環境の構築ができましたので、解決としたいと思います。

ありがとうございました。
1

スキルアップ／キャリアアップ（JOB@IT）