主キーとNULL制約

同姓同名の田中さんが、2 人目として入社してくる

社員テーブルの「その行を指すための列」を name にしていたとします。運用が始まって半年、2 人目の田中一郎さんが入社した時点で登録が弾かれます。かといって重複を許すと、今度は「田中一郎さんの住所を直す」という操作が、どちらの田中さんを指すのか決められません。

行を確実に 1 件だけ指すための列が主キーです。条件は 2 つあり、空欄が無いこと (NOT NULL) と、全行を通じて重複が無いこと (UNIQUE) を同時に満たす必要があります。

変わる値を、行の身元にしない

では email を主キーにすればよいかというと、これも後で困ります。メールアドレスは変わるからです。主キーが変わると、その値を参照している別の表の行まで、まとめて書き換えることになります。

業務上の意味を持たない連番の id を主キーに置き、「メールアドレスは重複しない」という業務の約束は UNIQUE で別に守ります。こうしておけば、メールアドレスが変わっても行の身元は変わりません。

商品コードや電話番号のように、業務の側で決められた一意な値のことを自然キーと呼びます。自然キーは意味が読めて便利なのですが、意味があるということは、業務の都合で変わりうるということでもあります。変わりうる値を身元にしない。 主キーを選ぶときの基準はこれだけです。

空欄は 0 でも空文字でもない

値が入っていない状態を、SQL では NULL と書きます。数値の 0 でも、長さ 0 の文字列でもありません。「まだ分からない」「そもそも該当しない」を表す、値の不在そのものです。発送日の列が NULL なら、それは「まだ発送していない」という意味になります。

ここで一度つまずきます。NULL は値ではないので、= では比べられません。

上の書き方が 0 件になるのは、条件が偽になるからではありません。分からない値と何かを比べた結果もまた分からないので、真にならず、行が残らないのです。空欄を探すときは IS NULL、値が入っている行を探すときは IS NOT NULL を使います。

逆に、空欄を許したくない列には NOT NULL を付けます。注文の金額や顧客の id のように、無い状態があり得ない列は最初から閉じておくと、取り出すたびに空欄の場合分けをしなくて済みます。

演習では users テーブルから、電話番号がまだ登録されていない人を探します。