railsに外部キー制約を追加してみた

October 22, 2014
rails rails4 foreigner 外部キー 外部キー制約 外部キー嫌い オライリー oreilly 図解

データベースに関して調べることって、業務的な側面から、わかりづらい単語や言葉ばっかり使って理解しづらいよね。 誰でも分かるように説明するのがプロなんだよなぁ。。。。まぁいいか。

プログラミングそのものに関してはそうでもないんだけどな。なので身近にあるもので例えてみる。

外部キー(foreign key)とは

リレーショナルデータベース(RDB)で、テーブルのある列に、別のテーブルの特定の列に含まれる項目しか入力できないようにする制約。また、その際に指定する列。

参考: e-words.jp

うん言葉で言ってもわかりづらいw

図で表してみる。

この左側の赤いやつが外部キー。別のテーブルの主キーを指してるでしょ?また、別のテーブルの主キーは参照キーと呼ばれる。

外部キーの問題って?

じゃあもし外部キーの参照先が更新されたり、削除されたらどうなるか。

animeテーブルのid = 2からid = 5に更新してみる。

anime_id = 2の状態ではあるけど、指している場所にデータがない。どこ指してるかわからない。

参照整合性が取れない。いわゆる迷子レコードになる。

なぜ外部キー制約を使うか

じゃあこれをどうするかというと、以下の2通りの方法を行う。

  1. アプリ側で監視する
  2. 外部キー制約を使う

普通は1を使うんだけど、1にも以下の問題点がある。

  • スクリプトを全ての参照に対して行う必要がある
  • チェックを毎日、何回も実行しなければならない
  • 参照が壊れていた場合でも修正できるかはわからない

この位の小さいテーブルだったらスクリプトでもいいけど、親、子、孫…と何個も組み合わさっているとチェックできるかは怪しい。

そこで2の手法、外部キー制約を使う。

ちなみに、これはオライリーのSQLアンチパターン外部キー嫌いって項目で書いてある。

この本、結構面白くてSQLでダメなことが一通り書いてある。 DB設計時にこれどうすればいいんだろうってのが結構解決する。俺でも分かったから、初心者でもわかりやすいと思う。 特にパスの閉包テーブルとポリモーフィックの単一テーブル継承(Single Table Inheritance、通称STI)にはお世話になりました。

外部キー制約の種類

外部キー制約は更新時(UPDATE ON)と削除時(DELETE ON)にそれぞれ設定できる。

外部キー制約は主に4種類。

  • NO ACTION
  • RESTRICT
  • CASCADE
  • SET NULL

SET DEFAULTとかもあるけど、今回は割愛。NO ACTIONRESTRICTは同じ。

RESTRICT (NO ACTION)

子が外部キーを持っていた場合、その親の操作をさせなくする。

CASCADE

子が外部キーを持っていた場合、その親が更新や削除したら、子もそれに追従する。

SET NULL

子が外部キーを持っていた場合、その親が更新や削除したら、子にnullを代入する。

Railsではどうなの?

RailsはActiveRecord経由で操作、つまりmodelに書く。

class User < ActiveRecord::Base
  has_one :animes, :class_name => 'Anime', :dependent => :destroyend

基本的には、:dependent => 制約名を追加するだけでいい。

でもこれはActiveRecord経由で操作するものだけにしか効果が無い

つまりuser1.destroyとかでは効いてもuser1.deleteでは効かない。

RailsはDB自体には制約ができない。なのでそれに対応するgem(foreigner)を入れる必要がある。

Gemの導入

Gemfileforeignerを追加。

gem 'foreigner'

実際に例で作成してみる

今回は次のようなテーブルの場合を考える。

usersテーブルにユーザー名、filesテーブルにファイル名。この2つが親。

playsテーブルが、どのユーザーが何のファイルを再生したかをカウントする、というもの。これが子。

playのロー(行データ)はusersテーブルとfilesテーブルに依存している。

親のロー(行データ)が更新したら子も更新し、削除されたら子も削除する。

モデルの作成は以下のコマンド。

$ rails g model user name:string
$ rails g model file name:string
$ rails g play count:integer file:references user:references

注意点は、一度テーブルを作成してからでないと反映できない。なので別にmigrationファイルを作成する。

$ rails g migration AddForeignKeyToPlays

AddForeignKeyToPlaysの部分は、AddForeignKeyToテーブル名複数形になる。

実際に作成されたmigrationファイルに外部キー制約を記入していく。書き方は3通り。

1. 繰り返しを使う

class AddForeignKeyToPlays < ActiveRecord::Migration
  def change
    change_table :plays do |t|
      t.foreign_key :users, options: 'ON UPDATE CASCADE ON DELETE CASCADE'
      t.foreign_key :files, options: 'ON UPDATE CASCADE ON DELETE CASCADE'
    end
  end
end

1.の場合、 t.foreign_key :users, options:とあるが、

t.foreign_key :親のテーブル名複数形, options: '付与するSQL制約'のように書く。

2. メソッドを使う

class AddForeignKeyToPlays < ActiveRecord::Migration
  def change
    add_foreign_key(:plays, :users, options: 'ON UPDATE CASCADE ON DELETE CASCADE')
    add_foreign_key(:plays, :files, options: 'ON UPDATE CASCADE ON DELETE CASCADE')
  end
end

2.の場合、 add_foreign_key(:plays, :users, options: 'ON UPDATE CASCADE ON DELETE CASCADE')とあるが、

add_foreign_key(:子のテーブル名複数形, :親のテーブル名複数形, options: '付与するSQL制約')のように書く。

3. メソッド(括弧なし)を使う

class AddForeignKeyToPlays < ActiveRecord::Migration
  def change
    add_foreign_key :plays, :users, options: 'ON UPDATE CASCADE ON DELETE CASCADE'
    add_foreign_key :plays, :files, options: 'ON UPDATE CASCADE ON DELETE CASCADE'
  end
end

3.の場合は2の括弧を外しただけ。

付与するSQL制約は、SQLによって効果が違う。 postgresqlの場合だと、以下の表通り。

種類 更新時(ON UPDATE)の効果 削除時(ON DELETE)の効果
NO ACTION 規定値。親が更新できない。 規定値。親が削除できない。
RESTRICT NO ACTIONと同じだが、制約の検査を遅らせられない NO ACTIONと同じだが、制約の検査を遅らせられない
CASCADE 親に合わせて子も更新 親に合わせて子も削除
SET NULL null値を代入 null値を代入
SET DEFAULT 自分で決めた規定値にする 自分で決めた規定値にする

なのでON UPDATE CASCADE ON DELETE CASCADEだと、更新時も終了時も親に合わせて更新、削除になる。