テーブル・行・列の基本

給与欄に「応相談」と書かれていたら、平均は出せない

社員の一覧を CSV で受け取ったとします。給与の欄には 500000 が並んでいるはずが、ところどころ「応相談」「50万」「500,000」が混ざっています。平均を出そうとした瞬間に計算が止まり、まず全行を目で確認する作業から始めることになります。

テーブルは、この事故を入り口で止めます。表を作るときに、列ごとに「入れてよい値の形」を宣言しておくからです。宣言に合わない値は、そもそも保存できません。

列は 1 つずつ、型を決めて作る

テーブルの定義は、列の名前と型を並べた形になります。

INT は整数、VARCHAR(50) は 50 文字までの文字列、DATE は日付です。金額の salary に付いている DECIMAL(10,2) は、小数点以下 2 桁まで誤差なく扱う型です。

型を決めておくと、入り口を守るだけでなく、取り出したあとの扱いも決まります。DATE の列なら日付として大小を比べられますし、INT の列なら合計や平均が出せます。すべてを文字列で持ってしまうと、"9" が "10" より後ろに来るような並び方をして、そのたびに変換の処理を書くことになります。

解説

金額に FLOAT や DOUBLE を使ってはいけません。2 進数の小数で近似するため、足し算を重ねると 1 円ずれます。請求書の合計がどうしても 1 円合わない、という事故はたいていここが原因です。

型のあとに、制約でもう一段しぼる

型は値の形を決めますが、それだけでは「空欄のまま通る」「同じ値が何度でも入る」を止められません。そこで列に制約を付けます。上の定義に出てきた NOT NULL は「空欄を許さない」、PRIMARY KEY は「この列で 1 行を見分ける」という宣言です。ほかに、重複を禁じる UNIQUE、省略されたときの値を決める DEFAULT があります。

制約が効くのは、アプリを通した書き込みだけではありません。管理画面から流した SQL でも、夜間に動くバッチでも、違反する行は同じように拒否されます。アプリのコードに書いたチェックは、そのコードを通らない書き込みには効きません。 最後の砦をデータベース側に置いておくのが、テーブル設計の基本方針です。

演習では employees から、必要な列だけを取り出します。表にはいくつも列がありますが、画面に出したいのはそのうち 3 つです。