Tips for debugging fixture foreign key errors in rails

Question

I'm getting a Foreign Key error in my fixture setup, and I'm stumped as to how to go about debugging it.

Specifically, I'm getting the error below, but I know the label exists:

RuntimeError: Foreign key violations found in your fixture data. Ensure you aren't referring to labels that don't exist on associations.

Here is the setup: I have Users, Categories, and Entries. Each category has a user, and each entry has 12 categories.

The offending Entry fixture is

bob_2022_01_10:
  date: 2022-01-10
  segment: 3
  user: bob
  category_0: test
  # categories can be null, so I don't set category_1 through category_11 for this example.

And, if I comment out the category_0 line, everything works.

The Category fixture is

test:
  id: 100
  name: Test category
  user: bob
  parent_id: nil

If I comment out the offending line in the Entry fixture, I can use the test fixture in other tests. (Hence, I know the problem isn't that I'm referencing a fixture that doesn't exist.)

I can set up the relationship between an entry and a category "by hand" in my test:

   entry = entries :bob_2022_01_10
   entry.category_0 = categories :test

I can also set up the fixture using a category_id instead of a fixture name:

bob_2022_01_10:
  date: 2022-01-10
  segment: 3
  user: bob
  category_0_id: 100

I wonder if the problem is the way I set up the foreign keys in Entry.rb:

class Entry < ApplicationRecord

  LAST_SEGMENT = 11

  belongs_to :user

  validate :validate_category_owner
  
  (0..LAST_SEGMENT).each do |time_segment|
    belongs_to "category_#{time_segment}".to_sym, class_name: "Category", foreign_key: "category_#{time_segment}_id", optional: true
   end

However, that would be odd because everything but the fixture setup works.

Any ideas what I might be doing wrong?

---

If it helps, I'll include the schema also:

ActiveRecord::Schema.define(version: 2022_01_15_023305) do

  create_table "categories", force: :cascade do |t|
    t.string "name"
    t.integer "order"
    t.integer "user_id", null: false
    t.integer "parent_id"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["parent_id"], name: "index_categories_on_parent_id"
    t.index ["user_id"], name: "index_categories_on_user_id"
  end

  create_table "entries", force: :cascade do |t|
    t.date "date"
    t.integer "segment"
    t.integer "user_id", null: false
    t.integer "category_0_id"
    t.integer "category_1_id"
    t.integer "category_2_id"
    t.integer "category_3_id"
    t.integer "category_4_id"
    t.integer "category_5_id"
    t.integer "category_6_id"
    t.integer "category_7_id"
    t.integer "category_8_id"
    t.integer "category_9_id"
    t.integer "category_10_id"
    t.integer "category_11_id"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["category_0_id"], name: "index_entries_on_category_0_id"
    t.index ["category_10_id"], name: "index_entries_on_category_10_id"
    t.index ["category_11_id"], name: "index_entries_on_category_11_id"
    t.index ["category_1_id"], name: "index_entries_on_category_1_id"
    t.index ["category_2_id"], name: "index_entries_on_category_2_id"
    t.index ["category_3_id"], name: "index_entries_on_category_3_id"
    t.index ["category_4_id"], name: "index_entries_on_category_4_id"
    t.index ["category_5_id"], name: "index_entries_on_category_5_id"
    t.index ["category_6_id"], name: "index_entries_on_category_6_id"
    t.index ["category_7_id"], name: "index_entries_on_category_7_id"
    t.index ["category_8_id"], name: "index_entries_on_category_8_id"
    t.index ["category_9_id"], name: "index_entries_on_category_9_id"
    t.index ["user_id"], name: "index_entries_on_user_id"
  end

  create_table "users", force: :cascade do |t|
    t.string "fname"
    t.string "lname"
    t.string "email"
    t.string "password_digest"
    t.boolean "admin"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "categories", "categories", column: "parent_id"
  add_foreign_key "categories", "users"
  add_foreign_key "entries", "categories", column: "category_0_id"
  add_foreign_key "entries", "categories", column: "category_10_id"
  add_foreign_key "entries", "categories", column: "category_11_id"
  add_foreign_key "entries", "categories", column: "category_1_id"
  add_foreign_key "entries", "categories", column: "category_2_id"
  add_foreign_key "entries", "categories", column: "category_3_id"
  add_foreign_key "entries", "categories", column: "category_4_id"
  add_foreign_key "entries", "categories", column: "category_5_id"
  add_foreign_key "entries", "categories", column: "category_6_id"
  add_foreign_key "entries", "categories", column: "category_7_id"
  add_foreign_key "entries", "categories", column: "category_8_id"
  add_foreign_key "entries", "categories", column: "category_9_id"
  add_foreign_key "entries", "users"
end

Answer 1

The problem here is the error RuntimeError: Foreign key violations found in your fixture data. Ensure you aren't referring to labels that don't exist on associations didn't any help, this actually replaced the real error.

  if ActiveRecord.verify_foreign_keys_for_fixtures && !conn.all_foreign_keys_valid?
     raise "Foreign key violations found in your fixture data..."
  end

According to the code above, you either disable verify_foreign_keys_for_fixtures

# config/application.rb
config.active_record.verify_foreign_keys_for_fixtures = false

or figure out what the error (db level) behind. In order to do that, you need to run manually the sql in the method all_foreign_keys_valid? above

def all_foreign_keys_valid?
  sql = <<~SQL
  #....
  SQL

    begin
      transaction(requires_new: true) do
         execute(sql)
      end

      true
    # rescue ActiveRecord::StatementInvalid
    #    false
    rescue => e
      puts e # this will show the db level error
    end
end

In my case, the db error was permission denied ..., i need to grant the db user to SUPERUSER, your case might not be the same. In the bottom line, the tip: run the sql manually to figure out the root cause.

Answer 2

I had the same problem today. If you set up an id in your fixture element then you can only reference it by the id and not the name of the fixture element.

So if you comment out id: 100 in your Category fixture, then you should be able to use category_0: test in your Entry fixture.