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

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

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

外部キー(foreign key)とは

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

参考: e-words.jp

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

図で表してみる。

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

外部キーの問題って？

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

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 ACTIONとRESTRICTは同じ。

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の導入

Gemfileにforeignerを追加。

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 CASCADE ON DELETE CASCADEだと、更新時も終了時も親に合わせて更新、削除になる。