hiyoko-programingの日記

プログラミングを勉強したてのひよっ子。　　　エンジニア目指して勉強中。

テーブルの構成要素

プログラミング・テキスト

テーブルとエンティティ

エンティティとは現実世界の概念をデータベースで扱うデータとした場合の呼び名。エンティティをデータベースのオブジェクトに変換するとテーブルになる。つまり、「エンティティ = テーブル」と考えてほとんど差し支えない。

成績管理アプリを作る場合を考えると生徒、科目、成績といったエンティティが存在する。
データベースにはそれらのエンティティに対応した生徒テーブル、科目テーブル、成績テーブルを作成することになる。

テーブルの行と列

テーブルは名前の通り表の形式で構成されている。
テーブルの行はレコード、列はカラムと言いますがそれぞれ表している意味が異なる。

  • テーブルの行（レコード）はエンティティの具体的なデータを表す
  • テーブルの列（カラム）はエンティティの属性を表す

テーブルの行（レコード）

レコードとはエンティティの具体的なデータ。例えば以下のような生徒テーブルのレコードを考えてみる。

id name email
1 山田太郎 taro@example.com
2 鈴木次郎 jiro@example.com

idが1である1行目は山田太郎という生徒のデータを管理している。idが2である2行目は鈴木次郎という生徒のデータである。このようにレコードはそのテーブルの表す具体的なデータ（山田太郎、鈴木次郎）を表している。

テーブルの列（カラム）

カラムとはエンティティの属性。先ほどの例だと、生徒テーブルにはid、name、email（それぞれ識別子、名前、メールアドレスの意味）という3つの属性を持っているということになる。

テーブル同士の関連性

エンティティ間には関係性のある場合がある。「エンティティ = テーブル」と考えて良いので、テーブル同士にも関係性がある場合がある。この関係性がリレーションにあたる。
例えば、生徒と成績の間には関係性がある。生徒は必ず成績を持っており、成績も必ずある生徒に紐付いている（Aさんは70点、Bさんは90点など）。このような場合、生徒テーブルと成績テーブルの間にはリレーションがある。

データを識別するための特殊な属性値

属性の中にはキーと呼ばれる特殊なデータが存在する。キーは同じテーブルのレコード同士を識別するためのデータ。多くの場合、idという名前のつく属性がキーとなる。

キーの役割

エンティティの属性であるカラムの中にはキーと呼ばれる特殊なデータが存在する。キーの役割はレコードを識別すること。

 キー

テーブルにおけるキーとはレコードを識別するための特別なカラムのことを指す。キーは識別子であるので同じテーブル内の他のレコードとは絶対に被らないように設定する。

具体的なキーの種類を見てみる。

キーの種類

キーには以下の2種類がある。

  • 主キー
  • 外部キー

主キー

主キーはあるテーブル内のレコードを判別するための識別子となるカラム。多くの場合idという名前のカラムが主キーとなる。

 主キー

主キーは、テーブルの中で他のレコードとの区別をつける識別子となるカラム。そのため、同じ主キーの値を持つレコードがテーブル内に存在してはいけない。

以下の生徒テーブルのidカラムが主キーになる。このとき、鈴木次郎のレコードのidが1であってはいけない。

id name email
1 山田太郎 taro@example.com
2 鈴木次郎 jiro@example.com

外部キー

外部キーは異なるテーブルのレコードと関係性（リレーション）を持つ場合に必要なカラム。主キー同様に識別子の役割を持つが、他のテーブルのレコードを識別するために使う。

 外部キー

外部キーは関連する他のテーブルのレコードの主キーを値として持つカラム。外部キーは他のテーブルのレコードとの関係性を表すために用いる。

主キーの説明であげた生徒テーブルには2名の生徒がいる。主キーとなるカラムはid。

id name email
1 山田太郎 taro@example.com
2 鈴木次郎 jiro@example.com

生徒テーブルと関係性を持つテーブルとして成績テーブルがあると仮定する。成績テーブルにはそれぞれの生徒に対応する成績が保存されている。

id score student_id
1 70 2
2 90 1

成績テーブルのidは主キーである。その他にstudent_idという属性が存在する。成績テーブルでは、このstudent_idは外部キーに当たる。これはその成績をとった生徒のレコードの主キーと対応している。
つまり成績テーブルのidが1であるレコードは生徒テーブルのidが2であるレコードと対応しており、このことから「鈴木次郎さんは70点である」ことが分かる。

制約で安全なテーブルを設計する

テーブルのカラムに対して制約をかけることで不正なデータや予期せぬデータが保存されることを防ぐことが出来る。

制約とは

制約とは特定のデータの保存を許さないためのバリデーション。例えば同じメールアドレスのユーザーを登録できないようにする、名前のデータが空のユーザーを保存できないようにするといったことができるようになる。

制約の種類

設定できる制約の中で主なものは以下の4つ。

  • NOT NULL制約
  • 一意性制約
  • 主キー制約
  • 外部キー制約

この４つの制約の挙動を具体的に確認するために実際に実装してみる。そこで、制約を学習するためのサンプルアプリを作成する。

 以下の手順で「DataBaseDesignSample」という名前のRailsアプリケーションを作成する

1. アプリケーションの作成以下のコマンドを順々に実行していく。

ターミナル
1
2
3
4
 
$ cd #ホームディレクトリに移動
$ rails _5.0.7.2_ new DataBaseDesignSample -d mysql #mysqlでRailsアプリケーションを作成
$ cd DataBaseDesignSample
$ bundle exec rake db:create #DBの作成

もし、アプリケーション作成段階でエラーが生じた場合は、以下のような赤字の文章が表示される（必ずしも同じ文面ではない）。

【例】ターミナル
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
Installing mysql2 0.5.3 with native extensions

Gem::Ext::BuildError: ERROR: Failed to build gem native extension.

current directory: /Users/user_name/Programs/web/foobar-repo/vendor/bundle/ruby/2.5.1/gems/mysql2-0.5.3/ext/mysql2

/Users/user_name/.rbenv/versions/2.5.1/bin/ruby -r

(中略)

An error occurred while installing mysql2 (0.5.3), and Bundler cannot continue.
Make sure that `gem install mysql2 -v '0.5.3'` succeeds before bundling.

エラーが表示されている場合は、以下のコマンドを実行する。

ターミナル
1
2
3
4
5
 
$ bundle config --delete build.mysql2
$ bundle config --global build.mysql2 --with-opt-dir="$(brew --prefix openssl)"

$ cd ~/projects/DataBaseDesignSample
$ bundle install

2. userモデルを作成

ターミナル
1
 
$ rails g model user

準備は以上。

NOT NULL制約

NOT NULL制約はカラムに設定する制約。NOT NULL制約を設定すると、そのカラムの値にはNULL（空の値）を入れることができなくなる。絶対に値が必要のあるカラムに対して使う制約。

 

例えば、usersテーブルのnameというカラムにNOT NULL制約を設定すると、nameが空(nil)レコードは保存できなくなる。

実際にNOT NULL制約の挙動を確認してみる。

 usersテーブルにNOT NULL制約を付けたnameカラムを作成

Railsでは、マイグレーションファイルでカラムを追加するときにnull: falseと記述することでNOT NULL制約を設定することができる。

マイグレーションファイル
1
2
3
4
5
6
7
8
 
class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string :name, null: false
      t.timestamps null: false
    end
  end
end

記述ができたらターミナルでマイグレーションを実行。

ターミナル
1
 
$ bundle exec rake db:migrate

問題なくマイグレーションが実行されてusersテーブルが作成されたら、rails cを使って実際の挙動を確認。

ターミナル
1
2
3
 
$ rails c
irb(main):001:0> User.create(name: "keita") //=> ユーザーが作成される
irb(main):002:0> User.create(name: nil) //=> エラー

NOT NULL制約が設定されたカラムがnilであるとエラーが発生する。

一意性制約

一意性制約はカラムに設定する制約。一意性とはユニークで他とは違うという意味。一意性制約を設定したカラムには同じ値を設定できなくなる。例えばAさんのemailが「test@gmail.com」だった場合、他にemailが「test@gmail.com」のレコードを保存できなくなる。

 

一意性制約はテーブル内で重複するデータを禁止する制約であるから、emailカラムに対して一意性制約を設定すると同じemailのレコードは保存できなくなる。

実際に一意性制約の挙動を確認してみる。

 usersテーブルに一意性制約を付けたemailカラムを作成

emailカラムを作成するためのマイグレーションファイルを作成。

ターミナル
1
 
$ rails g migration AddEmailToUsers email:string

テーブルのカラムに一意性制約をかけるときは、インデックスの作成も必要になる。全てのデータを検索しないと、過去のデータと重複しているかわからないため。

Railsでは、add_indexメソッドの中でunique: trueという引数を指定することで、一意性制約をかけるためのマイグレーションファイルを作成できる。

一意性制約
1
 
add_index :テーブル名, :カラム名, unique: true

生成されたマイグレーションファイルを以下のように編集してemailカラムに一意性制約を設定する。

マイグレーションファイル
1
2
3
4
5
6
 
class AddEmailToUsers < ActiveRecord::Migration
  def change
    add_column :users, :email, :string
    add_index :users, :email, unique: true
  end
end

記述ができたらターミナルでマイグレーションを実行する。

ターミナル
1
 
$ bundle exec rake db:migrate

実際の挙動をrails cで確認してみる。

ターミナル
1
2
3
 
$ rails c
irb(main):001:0> User.create(name: "taro", email: "taro@yamada.com") //=> ユーザーが作成される
irb(main):002:0> User.create(name: "yamada", email: "taro@yamada.com") //=> エラー

2行目と3行目でユーザーを作成しているが、3行目ではエラーが起きる。これは2行目と3行目で同じemailでユーザーを作成していることで、一意性制約に引っかかってしまったためである。
このように一意性制約を設定したカラムの値は、唯一の値でなくてはならない。

主キー制約

主キー制約とは、レコードが必ず主キーを持っていなくてはいけないことを保証するための制約。

 

主キー制約は、主キーである属性値が必ず存在してかつ重複していないことを保証する制約であり、主キーに対してNOT NULL制約と一意性制約を両方設定するのと同義になる。

Railsでテーブルを作成する際、主キー制約は元々実装されている。Railsでは主キーはidカラムとして自動で作成される。つまりidカラムの値は重複しないようにできている。

外部キー制約

外部キー制約とは、外部キーに対応するレコードが必ず存在することを保証する制約。例えばstudent_idが3のレコードを保存するためにはstudentsテーブルにidが3のレコードが存在していなくてはならない。

 

外部キー制約は、外部キーの対応するレコードが必ず存在しなくてはいけないという制約なので、外部キーのカラムに値があっても、その値を主キーとして持つ他のテーブルのレコードがなければいけない。

実際に外部キー制約の挙動を確認してみる。

 外部キー制約を実装

usersテーブルの外部キーを持つためのscoresテーブルを作成する。このscoresテーブルはユーザーの成績を保存するためのテーブルである。そのため、scoresテーブルのレコードはuser_idという外部キーのカラムを持ち、どのユーザーの得点なのかがわかるようにする。

ターミナル
1
 
$ rails g model score

Railsでは、マイグレーションファイルで外部キーとなるカラムを追加するときにforeign_key: trueと記述することで外部キー制約を設定することができる。

生成されたマイグレーションファイルを以下のように編集してuserとのアソシエーションに外部キー制約を設定する。

マイグレーションファイル
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
class CreateScores < ActiveRecord::Migration
  def change
    create_table :scores do |t|
      t.string :name
      t.integer :score
      t.references :user, foreign_key: true
      t.timestamps null: false
    end
  end
end

記述ができたらターミナルでマイグレーションを実行。

ターミナル
1
 
$ bundle exec rake db:migrate

マイグレーションを実行するとscoresテーブルにはuser_idというカラムが作成されている。このuser_idカラムは外部キーであり、外部キー制約が設定されている。

rails cで挙動を確認。usersテーブルが以下のような状態と仮定。

id name email
1 山田太郎 taro@example.com
2 鈴木次郎 jiro@example.com
ターミナル
1
2
3
 
$ rails c
irb(main):001:0> Score.create(name: "English", score: 80, user_id: 2) //レコードが生成される
irb(main):002:0> Score.create(name: "Math", score: 90, user_id: 4) //エラー

3行目では、user_idに4を指定している。しかし、usersテーブルにはidが4のユーザーは存在しないので、外部キー制約によってエラーが発生する。このように外部キー制約は関連先のテーブルに存在する主キーのみしか外部キーに指定することができない。

インデックスでデータの検索を高速化する

サービスでよく起きるテーブル操作の中でレコードの検索がある。例えばusersテーブル内をnameカラムで検索したい場合など。こういったテーブル内で検索が頻繁に行われるカラムにインデックスを設定することで検索の高速化を図ることができる。

インデックスとは

インデックスはデータベースの機能の一つで、テーブル内のデータ検索を高速化することができる。インデックスはカラムに対して設定することができ、設定したカラムでの検索が高速になる。
インデックスを設定することを、「インデックスを貼る」と言う

 インデックス

インデックスとはテーブル内のデータの検索を高速にするための仕組み。インデックスはカラムに対して設定する。インデックスをカラムに設定するとそのカラムで検索をした場合に検索速度が向上する。

インデックスのデメリット

インデックスで速度が上がるからといってすべてのカラムにインデックスを設定してはいけない。インデックスには以下の2つのデメリットがある。

  • データを保存・更新する速度が遅くなる
  • データベースの容量を使う

データを保存・更新する速度が遅くなる

データを保存する際に、設定されているインデックスの数だけ追加でデータを作成する。インデックスを設定するカラムが増えるだけ保存するデータが増え、処理の速度が遅くなる。

データベースの容量を使う

インデックスはそのカラムで検索しやすいための特別なデータを保存するために検索速度が向上する仕組み。そのため、インデックスを多く設定すればその分、データが必要になり容量が圧迫される。

1つのカラムに対するインデックス

テーブル内の1つのカラムにインデックスを貼る場合は、そのカラムで検索した場合に検索速度が向上する。
インデックスはmigrationファイル内で以下のように記述することで設定することができる。

migrationファイル
1
2
3
4
5
 
class AddIndexToテーブル名 < ActiveRecord::Migration
  def change
    add_index :テーブル名, :カラム名
  end
end

 1つのカラムに対するインデックスを設定してみる

DataBaseDesignSampleアプリケーションを使ってインデックスの設定を実践してみる。scoresテーブルに対してインデックスを貼るためのマイグレーションファイルを作成。

ターミナル
1
 
$ rails g migration AddIndexToScores

作成したマイグレーションファイルを編集してnameカラムにインデックスを貼る。

migrationファイル
1
2
3
4
5
 
class AddIndexToScores < ActiveRecord::Migration
  def change
    add_index :scores,  :name
  end
end

記述ができたらターミナルでマイグレーションを実行。問題なく実行できたらscoresテーブルのnameカラムに対してインデックスが設定できている。
以下のような検索の場合、検索速度が向上する。

nameカラムによる検索
1
 
Score.where(name: '山田太郎')

複数のカラムに対するインデックス

インデックスは1つのカラムだけではなく、複数のカラムにも設定ができる。例えば、ユーザーを姓と名で検索するシステムを作っていることを想定すると、SQLは以下のようになる。

姓と名によるユーザー検索
1
2
3
 
SELECT * 
FROM users
WHERE family_name = '山田' AND first_name = '太郎'

このように検索時に2つのカラムを使う場合が多いときに複数カラムに対してインデックスを設定する。
複数のカラムにインデックスを設定するためには、migrationファイル内で以下のように記述する。

migrationファイル
1
2
3
4
5
 
class AddIndexToテーブル名 < ActiveRecord::Migration
  def change
    add_index :テーブル名, [:カラム名, :カラム名]
  end
end

 複数のカラムに対するインデックスを設定してみる

DataBaseDesignSampleアプリケーションを使ってインデックスの設定を実践してみる。usersテーブルに対してインデックスを貼るためのマイグレーションファイルを作成。

ターミナル
1
 
$ rails g migration AddIndexToUsers

作成したマイグレーションファイルを編集してnameカラムとemailカラムの2つに対してインデックスを貼る。

マイグレーションファイル
1
2
3
4
5
 
class AddIndexToUsers < ActiveRecord::Migration
  def change
    add_index :users,  [:name, :email]
  end
end

記述ができたらターミナルでマイグレーションを実行。問題なく実行できたらusersテーブルのnameカラムとemailカラムの2つで検索する場合に対するインデックスが設定できている。
以下のような検索の場合、検索速度が向上する。

nameカラムによる検索
1
 
User.where(name: '山田太郎', email: 'taro@mail.com')

 この方法でインデックスを貼るとき、emailカラム単体で検索する場合には検索速度は向上しないので注意する。

まとめ

  • エンティティをテーブルとして定義する
  • エンティティの持つ属性をカラムとして定義する
  • カラムには主キーを必ず持たせる
  • 他のテーブルのレコードと関連がある場合、外部キーという形で他のテーブルとの関係を保存する
  • カラムの値には制約をつけてデータの正しさを保証する
  • 値が必ず設定されていることを保証するときにはNOT NULL制約を用いる
  • 値に重複がないように設定するには一意性制約を用いる
  • キーの存在を保証するときには主キー制約、外部キー制約を用いる
  • 検索する際に使うカラムにはインデックスを設定する