LoginSignup
3
1

More than 5 years have passed since last update.

@b_a_a_d_o

MySQLの外部キー制約をRuby on Railsでやってみた

Last updated at Posted at 2018-06-24

SQLアンチパターンに載っていたものをRailsで実装してみる。
RailsとMySQL関連はインストール済みである事を前提としている。

環境

Ruby: 2.6.0-preview2
Rails: 5.2.0
MySQL: 8.0

適当にアプリを作る

rails new bug_reports -d mysql

Gemfileに2つ書き足す。

gem 'therubyracer'
gem 'bcrypt'

上記gemを入れてからとりあえずデータベースを作成する。

bundle update
rake db:create

各モデルを作る

元のDDL

本に記載されていた、ベースになるDDLは以下の通り。

CREATE TABLE Accounts (
  account_id        SERIAL PRIMARY KEY,
  account_name      VARCHAR(20),
  first_name        VARCHAR(20),
  last_name         VARCHAR(20),
  email             VARCHAR(100),
  password_hash     CHAR(64),
  portrait_image    BLOB,
  hourly_rate       NUMERIC(9,2)
);

CREATE TABLE BugStatus (
  status            VARCHAR(20) PRIMARY KEY
);

CREATE TABLE Bugs (
  bug_id SERIAL     PRIMARY KEY,
  date_reported     DATE NOT NULL,
  summary           VARCHAR(80),
  description       VARCHAR(1000),
  resolution        VARCHAR(1000),
  reported_by       BIGINT UNSIGNED NOT NULL,
  assigned_to       BIGINT UNSIGNED,
  verified_by       BIGINT UNSIGNED,
  status            VARCHAR(20) NOT NULL DEFAULT 'NEW',
  priority          VARCHAR(20),
  hours             NUMERIC(9,2),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (verified_by) REFERENCES Accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE ON DELETE SET DEFAULT
);

枠の生成

これらっぽいモデルを作る。

rails g scaffold BugStatus status:string{20}
rails g scaffold Account account_id:primary_key account_name:string{20} first_name:string{20} last_name:string{20} email:string{100} password:digest portrait_image:binary 'hourly_rate:decimal{9,2}'
rails g scaffold Bug bug_id:primary_key date_reported:date summary:string{80} description:string{1000} resolution:string{1000} reported_by:bigint assigned_to:bigint verified_by:bigint status:string{20} priority:string{20} 'hours:decimal{9,2}'

decimalをシングルクォーテーションで囲っている理由は下記参照。
Rails g modelの際のdecimal型のフィールドについての注意点
password_hashの列名がpasswordとなっているが、bcryptの仕組みに従ったかたちにしている。

マイグレーションの加工

その後、生成された各マイグレーションを加工する。

db/migrate/20180623154725_create_bug_statuses.rb
class CreateBugStatuses < ActiveRecord::Migration[5.2]
  def change
    create_table :bug_statuses, id: false do |t|
      t.string :status, limit: 20, null: false
    end
    execute "ALTER TABLE bug_statuses ADD PRIMARY KEY (status);"
  end
end

bug_statusesはid: falseとexecute ADD PRIMARY KEYとnull: falseを変更。
なぜprimary_keyを使わずにexecuteでADD PRIMARY KEYを実行しているかについては、下記記事を参照。
Rails4 db:migrateでid以外のカラムにプライマリキーの設定を行う – PAYFORWARD

db/migrate/20180623163759_create_accounts.rb
class CreateAccounts < ActiveRecord::Migration[5.2]
  def change
    create_table :accounts, id: false do |t|
      t.primary_key :account_id
      t.string :account_name, limit: 20
      t.string :first_name, limit: 20
      t.string :last_name, limit: 20
      t.string :email, limit: 100
      t.string :password_digest
      t.binary :portrait_image
      t.decimal :hourly_rate, precision: 9, scale: 2
    end
  end
end

accountsはid: falseのみ変更。

db/migrate/20180623164335_create_bugs.rb
class CreateBugs < ActiveRecord::Migration[5.2]
  def change
    create_table :bugs, id: false do |t|
      t.primary_key :bug_id
      t.date :date_reported, null: false
      t.string :summary, limit: 80
      t.string :description, limit: 1000
      t.string :resolution, limit: 1000
      t.bigint :reported_by, null: false
      t.bigint :assigned_to
      t.bigint :verified_by
      t.string :status, limit: 20, null: false, default: "NEW"
      t.string :priority, limit: 20
      t.decimal :hours, precision: 9, scale: 2
    end
    add_foreign_key :bugs, :accounts, column: :reported_by, primary_key: :account_id, on_update: :cascade, on_delete: :restrict
    add_foreign_key :bugs, :accounts, column: :assigned_to, primary_key: :account_id, on_update: :cascade, on_delete: :restrict
    add_foreign_key :bugs, :accounts, column: :verified_by, primary_key: :account_id, on_update: :cascade, on_delete: :restrict
    add_foreign_key :bugs, :bug_statuses, column: :status, primary_key: :status, on_update: :cascade, on_delete: :restrict
  end
end

bugsはid: falseとadd_foreign_keyとnull: falseを変更している。
書籍中はstatus列はSET DEFAULTになっているが、MySQLには無いので割愛する。

テーブルの生成

マイグレーションを実行してテーブルを生成する。

rake db:migrate

モデルに外部キーを設定する

同じモデルから複数のキーで同じモデルを参照しているため、belongs_to/has_manyを少し工夫している。

app/models/bug.rb
class Bug < ApplicationRecord
  belongs_to :reporter, class_name: "Account", foreign_key: "reported_by"
  belongs_to :assignee, class_name: "Account", foreign_key: "assigned_to", required: false
  belongs_to :verifier, class_name: "Account", foreign_key: "verified_by", required: false
end
app/models/account.rb
class Account < ApplicationRecord
  has_secure_password
  has_many :reported, class_name: "Bug", primary_key: "account_id", foreign_key: "reported_by"
  has_many :assigned, class_name: "Bug", primary_key: "account_id", foreign_key: "assigned_to"
  has_many :verfied, class_name: "Bug", primary_key: "account_id", foreign_key: "verified_by"
end

データを作ってみる

Railsのコンソールを起動する。

rails c

以下のようなデータを作る。

BugStatus.create(status: "opened")
BugStatus.create(status: "fixed")
BugStatus.create(status: "updated")
Account.create(account_id: 1, account_name: "def", first_name: "tarow", last_name: "okamoto", email: "okamoto@example.com", password: "abc", portrait_image: "none", hourly_rate: 3.4)
Bug.create(bug_id: 1, date_reported: "2018-06-23", summary: "hoge", description: "fugahoge", resolution: "nya", reported_by: 1, assigned_to: nil, verified_by: nil, status: "opened", priority: "important", hours: 1.2)

データを変更してみる

データを変更した時の値の変化を確認する。

bs = BugStatus.find("opened")
bs.status = "open"
bs.save
Bug.find(1).status
# => "open"
# opened から open に自動的に更新されている

a = Account.find(1)
a.account_id = 10
a.save
Bug.find(1).reporter.id
# => 10
# 1 から 10 に自動的に更新されている

BugStatus.find("open").destroy
# => ActiveRecord::StatementInvalid (Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails (`bug_reports_development`.`bugs`, CONSTRAINT `fk_rails_2002328f52` FOREIGN KEY (`status`) REFERENCES `bug_statuses` (`status`) ON UPDATE CASCADE): DELETE FROM `bug_statuses` WHERE `bug_statuses`.`status` = 'open')
# bugs テーブルに値が使用されている列があるため、ON DELETE RESTRICT制約に従って削除できない

before/afterフィルタの挙動

AccountモデルとBugモデルにそれぞれフィルタを仕掛けてみる。

  before_save :before_save
  after_save :after_save
  before_destroy :before_destroy
  after_destroy :after_destroy
  def before_save
    pp "before_save:#{self}"
  end
  def after_save
    pp "after_save:#{self}"
  end
  def before_destroy
    pp "before_destroy:#{self}"
  end
  def after_destroy
    pp "after_destroy:#{self}"
  end

予想通りではあるが、カスケードされた側（bugsテーブル側）のbefore/afterフィルタは動作しないので、自前で対応する必要がありそうだ。

a = Account.find(10)
a.account_id = 1
a.save
# "before_save:#<Account:0x0000000004a77688>"
#   Account Update (0.7ms)  UPDATE `accounts` SET `account_id` = 1 WHERE `accounts`.`account_id` = 10
# "after_save:#<Account:0x0000000004a77688>"
3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1