CODEWITHSUNDEEP
rubypostgresql
Jeff Zivkovic
Jeff Zivkovic

Reputation: 587

Insert violates foreign key constraints

My models:

class Team < ApplicationRecord
    belongs_to :objective
    belongs_to :consultancy
    has_one :seminar, :through => :consultancy

    belongs_to  :consultant, class_name: "Student"

    has_and_belongs_to_many  :users
end

class User < ApplicationRecord
    has_and_belongs_to_many  :teams
end

My function:

def find_placement(student)
    team = @consultancy.teams.detect{|x| x.has_room && student.objective_students.find_by(:objective => x.objective).obj_ready_and_willing?}
    team.users << student if team && team.valid? && team.persisted?
    return team
end

And this is the error I'm currently receiving: 

PG::ForeignKeyViolation: ERROR: insert or update on table "teams_users" violates foreign key constraint "fk_rails_8299e376e9" DETAIL: Key (team_id)=(3396) is not present in table "teams". : INSERT INTO "teams_users" ("user_id", "team_id", "created_at", "updated_at") VALUES ($1, $2, $3, $4)

This error only occurs in production environment, not development or testing. I've tried using console mode to manually run each line of the function. No error is triggered.

From this question https://dba.stackexchange.com/questions/29147/postgresql-insert-update-violates-foreign-key-constraints, I found this explanation.

"If a new row is inserted, check DataID and Address: if they contain a non-NULL value (say 27856), then check Table1 for DataID˙and Table2 for Address. If there is no such value in those tables, then return an error."

If I understand this paragraph correctly, my function is trying to insert a student into a team that doesn't exist. But doesn't the "if team && team.valid? && team.persisted?" part of my code take care of that? If not, what is a better way to insert the student into a team, but only if the team already exists.

Thank you in advance for any insight.

Upvotes: 2

Views: 399

Answers (2)

Jeff Zivkovic
Jeff Zivkovic

Reputation: 587

The key to getting past this was to reload.

Before, @consultancy was collecting all of the Teams, including some that had been destroyed. 

@consultancy.reload.teams

only collects the teams that still exist.

I would not have come to this conclusion without the comment from @SergioTulentsev and the answer from @lacostenycoder. Thanks!

Upvotes: 1

lacostenycoder
lacostenycoder

Reputation: 11226

The valid? method does not check database persistence, so you may or maynot need it. You'll probably want to insure the team model is persisted and not deleted from the teams table. To be safe, you might check both. ActiveRecord#persisted? method:

def find_placement(student)
  if team = @consultancy.teams.detect{|x| x.has_room && student.objective_students.find_by(:objective => x.objective).obj_ready_and_willing?}
    team.users << student if team.valid? && team.persisted?
  end
  team
end

Upvotes: 1

Related Questions