CDBTプラグインを複合主キー型テーブルに対応させるべきか

別件でUnicodeの「サロゲートペア」について調べていたら、データベースの「サロゲートキー」と「複合主キー」についての記事が目に留まって思いっきり脱線した次第。で、最近抱えていたデータベースの複合主キーについてのとある悩みがフューチャーされて来たので、脳内情報を整理する意味で記事を書いてみた。

現在のところ、ウチで公開している Custom DataBase Tables (以下、CDBT)プラグインは複合主キーを持つテーブルへの操作を上手くできない。特に、データの登録や編集、削除など、サロゲートキー(連番の代理キー)をプライマリキーとしているテーブルに対して、唯一なユニークレコードの絞り込みを行っているため、複数のナチュラルキーの値を複合化したものをプライマリキーとするいわゆる複合主キー型テーブルに対しては正確な絞込みが行えない状況なのだ。
まずここで、あまりデータベースに慣れ親しんでいないと、「サロゲートキー」「複合主キー」「ナチュラルキー」って何?─となるはずなので、自分自身のおさらいも兼ねてまとめておこう。

プライマリキー(主キー)

データベースのたいていのテーブルに存在する、データを一意に管理するためのキー制約。このキーが指定された列中には重複する値を入れることができず(ユニークキー制約)、空白(NULL)にすることもできない。

ユニークキー(一意キー、ユニークインデックス)

このキーが指定されている列中には重複する値を格納できず、一意にならなければならないというキー制約。ただし、プライマリキーと異なり空白(NULL)は格納可能で、空白(NULL)のみは重複ができる。このキーが指定されると同時にインデックスも貼られるため、ユニークインデックスと同義である。

サロゲートキー(代理キー)

オートインクリメント属性等により、連番の数値が格納されているユニークな値を持つカラムに対してプライマリキー制約を付与した場合のプライマリキーのこと。テーブル内で一意となる唯一の行レコードを特定するための列の集合(ナチュラルキー)を代替するため代理キーとも言われる。単一の列で全行レコードの一意性を識別できるのでシステム的に有益なのだが、実サービス等に利用するデータとしては意味をなさず、データ利用者側の観点からだとデータベースの物理ストレージ領域を無駄に圧迫する列でしかない。

ナチュラルキー(自然キー)

テーブル内の行レコードを特定するためのユニークキー制約のついた列の集合のこと。行レコードの一意性が識別できれば単一の列でもかまわないが、例えば、WordPressだと「投稿ID」「タグID」の組み合わせで、とあるタグに属する投稿データを一意に識別できるような場合、それら2列の集合体がナチュラルキーと呼ばれ、そのナチュラルキーを持つテーブルがwp_term_relationshipsだ。原則的にナチュラルキーに含まれる列はユニークキー制約をもっていなければならないため、プライマリキーに設定されていることが多い。データ利用者側の視点では、意味あるデータのみによって一意性が識別されるナチュラルキー型テーブルは用途が明確で無駄がない(と見えるようだ)。

複合主キー

複数プライマリキー、サロゲートキー+ナチュラルキー、プライマリキーなしの複数列集合のナチュラルキーのみといったテーブルが複合主キー型のテーブルといえる。要は単一列のみで行レコードの一意性が識別できないタイプのテーブルである。他のテーブル同士をリレーションさせるために外部キーの結合のみを管理するようなリレーションテーブルによく見られるタイプ(ナチュラルキーの項で紹介した、WordPressのwp_term_relationshipsテーブルなど)。リレーションテーブルを覗いたことがある人やDB設計したことがある人には良く理解できるはずだが、このような複合主キー型テーブルはそれぞれの外部テーブルのサロゲートキーを集中管理するような構造になっていて、実データのリレーション関係が一目ではわからないのが常だ。かといって、ナチュラルキーをプライマリキーとしたテーブルのプライマリキーを外部キーとしてリレーションさせるとそのリレーションテーブルを見るだけでデータの結合性が一目でわかるものの、参照コストの高い文字列データ等がデータベース内に重複して存在することになって、データベース全体としてパフォーマンス低下を招く上、物理ストレージ領域の圧迫もサロゲートキー以上に進んでしまう。
また、特にナチュラルキーが一意性識別に関与するようなテーブルだと、ユニークなデータを取り扱う際のシステムコストが高くなる。

言葉の整理をしてて改めて感じたが、複合主キーのテーブルを操作するのって超面倒くさそうだ。まぁ、実際に面倒なんだが。例えば、複合主キーのナチュラルキーのうち一つの列の値を変更したい場合、その行レコードを特定するために同じナチュラルキーを検索条件に入れなければならなかったりして、ちょっと処理を考えるのがイヤになる。
プライマリキーが二つ以上あるテーブルをUPDATEしようとするとMySQLに怒られたりするし、この複合主キー型テーブルに対応するのは当面先送りだろうなぁ…。

つくづく、phpMyAdminは偉大なアプリケーションだと思った今日この頃である。

Leave a Reply

Your email address will not be published.