外部キーの概要と制約を使うことのメリット・デメリット

外部キーとは

テーブル同士の紐づけに用いるカラムのこと。

users テーブル と user_login_histories テーブル が合った時に、 user_login_histories テーブル に user_id があったら
user_login_histories.user_id の値は users テーブル において主キー、 user_login_histories テーブル では外部キーと呼ばれる。

主キーと外部キーはRDBにとって、それぞれのテーブルを関連付けるために使用するとても大切な機能。

外部キー制約とは

主キーと外部キーを使った制約で利用した場合、下記の制限が入る。

1. 存在しない値を外部キーとして登録することはできない
2. 子テーブルの外部キーに値が登録されている親テーブルのレコードは削除できない

1. 存在しない値を外部キーとして登録することはできない

user_login_histories.user_id カラムに対し、users.id の値として存在しない値を登録しようとした場合エラーが出る。

例えばusersテーブルに下記のレコードが登録されている時に

mysql> SELECT * FROM users;
+----+------+
| id | name | 
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+

下記の用に user_login_histories.user_id として、 users.id カラムに存在しない値を登録しようとするとエラーになる。

INSERT into
  user_login_histories
VALUES
  -- id, user_id, loggined_in_at
  (1, 3, '2020-xx-xx xx:xx:xx');

2. 子テーブルの外部キーに値が登録されている親テーブルのレコードは削除できない

親テーブルのレコードを削除しようとした時に、レコードの主キーの値が子テーブルに外部キーとして登録されているとエラーが出て削除できない。

例えば下記のようなレコードが存在している時、usersテーブルのidが1のレコードを削除することはできないようになる(親がいない子レコードが作れないようにしてくれる)。

mysql> select * from users;
+----+------+
| id | name | 
+----+------+
|  1 | hoge | -- 削除できない
|  2 | fuga | -- 削除できる
+----+------+

mysql> select * from user_login_histories;
+----+---------+---------------------+
| id | user_id | logged_in_at        |
+----+---------+---------------------+
|  1 |       1 | 2020-xx-xx xx:xx:xx |
|  2 |       1 | 2020-xx-xx xx:xx:xx |
+----+---------+---------------------+

users.id が1のレコードを削除するにはまず子テーブルのレコードを削除しないといけない。

※ Railsを使っている場合、アソシエーション定義のオプションである dependent: :destroy などがこれを行ってくれる。

外部キー制約の種類

筆者は RESTRICT と CASCADE しか使ったことがありません。

• RESTRICT
  • 親テーブルのレコードに対し、削除または更新を行うとエラーとなる。設定を省略した場合 RESTRICT が設定される
• NO ACTION
  • RESTRICTと同じ
• CASCADE
  • 親テーブルのレコードに対し、削除または更新を行うと、子テーブル内で同じ値を持つカラムのデータに対して削除または更新を行う
• SET NULL
  • 親テーブルのレコードに対し、削除または更新を行うと、子テーブルの同じ値を持つカラムの値が NULL になります。
• SET DEFAULT
  • この設定を行うとテーブルの作成が行えなくなる

外部キー制約のメリット・デメリット

メリット

• 存在しない値が外部キーとして登録されることを防ぐことができる
  • データの整合性が保てる
• うっかり親テーブルのレコードを消しちゃった。なんてことがなくなり、子テーブルのレコードの親子関係がバグることがない

デメリット

• 親テーブルのレコード削除がめんどくさいかも
  • Railsでは dependent オプションがあるのでそんなに大変ではない印象
• 設定を間違えた際に、意図せず重要なレコードが消える可能性がある
• 大量のデータを抱えた親レコードがあっても、外部キー制約があると分割して消すことが難しいため、削除の負荷を分散できない
• DBを跨いだ制約はかけることができない

Ruby On Rails での外部キー制約の貼り方

新規でテーブルを作る場合は下記のような形で貼ることができる。

create_table(:users) do |t|
  t.string :name, null: false
end

create_table(:user_login_histories) do |t|
  t.references :user, null: false, foreign_key: true
end

OR

create_table(:users) do |t|
  t.string :name, null: false
end

create_table(:user_login_histories) do |t|
  t.references :user
end

# add_foreign_keyは後から外部キー制約を貼りたい場合にも使える
add_foreign_key(:user_login_histories, :users)

references(:xxx) だけでは外部キー制約は貼られないので注意。

では Rails の references型 は何をしているのか

1. xxx_id ではなく、 xxx と書くだけで xxx_id の形にしてくれている
2. インデックスを自動で貼っている

create_table(:user_login_histories) do |t|
  # user_login_histories.user_idカラムを作成
  # user_login_histories.user_idにインデックスを貼る
  t.references :user, null: false
end

外部キーを貼る時に気をつけること

下記のように add_foreign_key, add_index メソッドを実行しているプロジェクトがあったりする。
add_foreign_key は対象の外部キーカラムに既にインデックスがあればそれを使い回すし、なければ暗黙的にインデックスを貼る挙動をするため、この場合user_login_histories.user_id カラムに対して２つのインデックスを作成してしまう。

add_foreign_key(:user_login_histories, :users)
add_index(:user_login_histories, :user_id)

外部キーを貼る場合は下記のように先にインデックスを貼ってから、外部キーを貼る、という処理順序になるようにしておく必要がある。

add_index(:user_login_histories, :user_id)
add_foreign_key(:user_login_histories, :users)

OR

create_table(:user_login_histories) do |t|
  # user_login_histories.user_idカラムを作成
  # user_login_histories.user_idにインデックスを貼る
  t.references :user, null: false
end

add_foreign_key(:user_login_histories, :users)