can't delete object due to foreign key constraint

Question

I'm trying to do a simple user.destroy but running into the following error:

ERROR: update or delete on table "users" violates foreign key constraint "fk_rails_5373344100" on table "identities" DETAIL: Key (id)=(2) is still referenced from table "identities".

Here is my migration for Identities

class CreateIdentities < ActiveRecord::Migration
  def change
    create_table :identities do |t|
      t.references :user, index: true, foreign_key: true
      t.string :provider
      t.string :uid

      t.timestamps null: false
    end
  end
end

Here is my user and identity model:

class Identity < ActiveRecord::Base
  belongs_to :user

  validates_presence_of :uid, :provider
  validates_uniqueness_of :uid, :scope => :provider

  def self.find_for_oauth(auth)
    find_or_create_by(uid: auth.uid, provider: auth.provider)
  end
end

and user:

class User < ActiveRecord::Base
  TEMP_EMAIL_PREFIX = 'ricky@writeit.com'
  TEMP_EMAIL_REGEX = /\ricky@writeit.com/

  # Include default devise modules. Others available are:
  # :lockable, :timeoutable
  devise :database_authenticatable, :registerable, :confirmable,
         :recoverable, :rememberable, :trackable, :validatable, :omniauthable

  validates_format_of :email, :without => TEMP_EMAIL_REGEX, on: :update
...

end

I'm new to foreign keys and references, so I'm not sure at all how to fix this.

score 65 · Accepted Answer · answered Apr 09 '15 at 17:13

You would need to remove the Identity that references the user first. Then you can delete the user.. By default the foreign key is doing a restrict so you cannot delete the user if anything references to it.

if you would like use Rails to handle destroying the identity you can do

class User < ActiveRecord::Base
  has_many :identities,  dependent: :destroy 

  ......

 end

Which would cause Rails to destroy all the dependent records.

But as you are using Foreign keys, you can adjust your migration to set cascade deletes

 add_foreign_key :identities, :users, on_delete: :cascade

Assuming rails 4.2 which has native support

Awesome! But just a small thing, he need to remove the foreign key first then add it again with on_delete: :cascade — tkhuynh, Feb 23 '17 at 01:29

score 3 · Answer 2 · answered Apr 09 '15 at 17:12

An easy solution is to simply cascade-delete the records in the associated table, which can be done through active record, like so:

user.rb

class User < ActiveRecord::Base
  has_many :identities, dependent: :destroy

  # rest of user class
end

Check out the documentation pertaining to has_many for more info.

can't delete object due to foreign key constraint

2 Answers2

Linked

Related