株式会社リゾーム 技術部 システム開発 第1グループの宮野です。
「スッキリわかるSQL入門」読書会の第9回レポートです。
「スッキリわかるSQL入門」の過去のレポートや、他の読書会のレポートはカテゴリ「読書会」でご覧いただけます。
読書会の題材
前回に引き続き「スッキリわかるSQL入門」を題材としています。
9回目レポート
9回目は第10章「テーブルの作成」のレポートになります。参加者は4名でした。
それぞれの感想・意見交換
10.1 SQL命令の種類
10.1.1 データベースを使う2つの立場
- DBMSに対して、①データの操作を指示する立場 と、②データベース自体の操作を指示する立場 の2つがある。これらを分けて考えることが重要なんだな。今まであまり意識したことがなかった
10.1.2 4種類の命令
10.1.3 DCLとは
- DCL（GRANT, REVOKE）は普段、業務で権限を付与したりすることがないため一度も使ったことがない
- PostgreSQLの勉強をした際に権限の付与をしたぐらい
- SQLServerでは下記のような記述らしい
- GRANTやREVOKEを実際に使うとしたらどんな状況か？
- 開発・テスト環境ではあまり意識する必要はない
- 設定するとしたら最初の環境構築時？
- 特定の権限（例えばINSERTとか）を剥奪できるのは使いようがありそうだと思った
- 既存のデータで集計するだけのユーザーにはINSERTやUPDATEを制限しておく
- 必要がないときは閲覧権限だけのユーザーで入るようにしておくと事故防止になる
- 普段は開発環境しか触らないので、権限周りは何も考えずデータベース管理者にしてしまっている...
- サーバやDBの設定周りの知識は殆ど無いため、今後身に付けていきたい
10.2 テーブルの作成
10.2.1 テーブル作成の基本
- 頻繁にテーブル作らないので構文忘れがち
- CREATE TABLEはテスター業務ではあまり使わない
- テーブルがリリース媒体に含まれない改修のテストの際に、自分でCREATE TABLEしたことがあるくらい
- CREATE TABLEはよく使う
- RailsではActiveRecord経由でテーブルの作成やテーブル定義の変更を行うため、直接SQL文を使う機会はあまりない
10.2.2 デフォルト値の指定
- DEFAULT制約は各種フラグやステータスコード（0：未設定、1：男性、2：女性とか）を表すカラムによく設定する
10.2.3 DROP TABLE文
- たとえローカルDBでも、一度DROPしてから再度新しいテーブル定義でCREATEし直す方が間違いがなくて安心する
- （P319のコラムの内容に関して）現在の業務でテーブルが存在するときのみ削除するには、OBJECT_ID関数を使って存在するかどうかを確認している
- SQL ServerだとDDLのロールバックは行えるらしい
- DDLであるDROP TABLEをロールバックできるかがベンダーによって異なるのが怖い。少なくとも、私が触ったことのあるSQL Server/PostgreSQLではサポートされていたが、「トランザクションがあるから安心でしょ」と油断してはいけない
- 一部DBMS（Oracle DB等）ではDDLの操作はロールバック出来ないらしい！今後もしかすると他のDBMSも触る機会があるかもしれないので覚えておこう
10.2.4 ALTER TABLE文
- ALTER TABLEを使ったことはほぼない（あってもローカルDBだけ）
10.3 制約
10.3.1 人為的ミスに備える
- 制約はトランザクションとは異なり人為的ミスに対して有効
- 人為的ミスを防ぐために制限を課すというのは、SQLに限らず一般的な考え方。人はミスをする生き物だから
- テストする側も制約のことまで気を付ければ無駄な作業（NOT NULL制約の項目で「NULLの場合は…」というテストケースを書いてしまうなど）がなくなるので気を付けていきたい
10.3.2 基本的な3つの制約
- CHECK、UNIQUEは使ったことがない（NOT NULL、主キー制約はよく使う）
- そういえばUNIQUE制約は現在の業務ではあまり見ないような…（NULLを許可する必要がある場合が少ない？）
- 主キー制約、NOT NULL制約、外部キー制約を一番よく使ってたと思う
- UNIQUE制約を付けていても、NULLは重複できる。NULL同士を比較してもイコールじゃないから。なるほど…
- 特別なカラムでない限り、基本的にはNOT NULL制約をつけるようにしている
- そもそもNULLを許容したくないため
- プログラムの中で値がNULLだった場合の条件分岐をなくすることができる
- NULLが入ること前提の設計の場合もある（カスタム次第で特定のカラムを使用したりしなかったり。使用しない場合はすべてNULL）
- 制約でカラムに入りうる値を制御するケースについて
- 練習問題で血液型カラムに入る候補をCHECK制約で制限しているが、これだと後で候補を変える必要が出たとき、DBの設定変更になってしまう
- 自分なら血液型マスタテーブルを作成して外部キー参照にする
- ビジネスロジックがかかわる場合、CHECK制約は結構使いにくいかも
10.3.3 主キー制約
- 主キーの指定はカラム定義の横に記載する単独列指定ではなく、CREATE TABLE文の末尾に記載する複合主キー指定の書き方をよく使う（主キーが1カラムだけの場合も後者を使うよう統一している）
- （P331のコラムの内容に関して）主キーとして用いている列には主キー制約を明示的に付けるべきだと感じた
10.4 外部キーと参照整合性
10.4.1 参照整合性の崩壊
- データ自体がおかしくなってしまうとそれの復旧作業などで製品の稼働を止めてしまうので気をつけるところ
- テーブル設計を行う上でとても重要な概念だと思う
- 整合性が崩れると調査もかなり大変だろうなぁ
10.4.2 崩壊の原因
- 参照整合性の崩壊は「存在しない行を参照する」パターンより「ほかの行から参照されている」行を編集することで起きるパターンのほうが意図せず起きそう
- データの更新、削除をする際には念には念を入れて確認することが大切
- それでもミスをする可能性はあるので、10.4.3の外部キー制約が重要になってくる
10.4.3 外部キー制約
- テーブルの内容によっては外部キー制約をつけずに他テーブルのIDを保存する場合もある
- 履歴テーブルなど（元IDが削除されても履歴は残っていてほしい）
- 「主キー制約が設定されていなくても、利用者が「行を識別するための列」として利用する列があれば、それは主キー列」
- これは盲点だった。今後テーブル設計に携わる際には上記を意識していきたい
章末
- TRUNCATEもよく使う（確実にテーブル内の全行を削除する時など）
- TRUNCATEもSQL Serverではロールバックが可能
- SQL ServerのDELETEとTRUNCATEは両方ロールバック可能だが、記録の残し方が違う
まとめ
「いつもはこんなことに気を付けている」「自分ならこう書く」といった日々の業務に基づいた発言が多く聞かれました。
また、この章で扱われている内容はミスしたときにDB全体に及ぶ影響が大きいため、念を入れて確認しながら操作しなくては…という声も多かったです。
次回は第11章「さまざまな支援機能」を読んでいきます。
