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

Question

I run a single test with

rake test TEST=test/system/my_test.rb

and see this:

rake test TEST=test/system/my_test.rb
Running 1 tests in a single process (parallelization threshold is 50)
Run options: --seed 48133

# Running:

E

Error:
myTest#test_visiting_the_index:
RuntimeError: Foreign key violations found in your fixture data. Ensure you aren't referring to labels that don't exist on associations.
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-7.0.2.4/lib/active_record/fixtures.rb:633:in `block in insert'
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-7.0.2.4/lib/active_record/fixtures.rb:621:in `each'
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-7.0.2.4/lib/active_record/fixtures.rb:621:in `insert'
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-7.0.2.4/lib/active_record/fixtures.rb:607:in `read_and_insert'
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-7.0.2.4/lib/active_record/fixtures.rb:567:in `create_fixtures'
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-7.0.2.4/lib/active_record/test_fixtures.rb:268:in `load_fixtures'
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-7.0.2.4/lib/active_record/test_fixtures.rb:122:in `setup_fixtures'
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-7.0.2.4/lib/active_record/test_fixtures.rb:10:in `before_setup'
    /Users/st/.rbenv/versions/3.0.3/lib/ruby/gems/3.0.0/gems/activesupport-7.0.2.4/lib/active_support/testing/setup_and_teardown.rb:40:in `before_setup'


rails test test/system/my_test.rb:12



Finished in 0.190845s, 5.2399 runs/s, 0.0000 assertions/s.
1 runs, 0 assertions, 0 failures, 1 errors, 0 skips

The critical part being:

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

Is there any way to narrow down which fixture(s) the error is emanating from?

What I've tried

• I tried to see if I can load a single fixture at a time in the rails console, that way I could figure out which is causing it (but no luck doing that)
• I see a similar conversation here.

Mctyre

I have a suggestion: did you just add a new migration subsequently to the test with relations? if tests ran properly before the migration, then the fixtures of the newer class might be the culprit. – Bridewell

I wish that's what happened. I'm 300+ commits into an MVP - a "sketch" of an app (to see if an idea could work). So I hadn't run rails test until today at the 300 commit mark. It could be argued I'm getting just deserts for neglecting tests this long. – Mctyre

Rollback. To the first class, run the test(s). Then iterate with each additional class. Tedious, I know, but there really are no good choices. – Bridewell

@Bridewell that’s a good (better) idea than what I did – Mctyre

Answer 1

If you're using postgres, check the database logs:

ERROR:  insert or update on table "friendships" violates foreign key constraint "fk_rails_e3733b59b7"
DETAIL:  Key (user_id)=(999) is not present in table "users".

You can check the integrity yourself as well. You'll get an error from fixtures, but the records should stay in the database. Reset to make sure there are no leftovers.

RAILS_ENV=test bin/rails db:reset
RAILS_ENV=test bin/rails db:fixtures:load
RAILS_ENV=test bin/rails c 

Run this in the console, you should get the same error as the log above.

ActiveRecord::Base.connection.execute(<<~SQL)
  do $$
    declare r record;
  BEGIN
  FOR r IN (
    SELECT FORMAT(
      'UPDATE pg_constraint SET convalidated=false WHERE conname = ''%I''; ALTER TABLE %I VALIDATE CONSTRAINT %I;',
      constraint_name,
      table_name,
      constraint_name
    ) AS constraint_check
    FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY'
  )
    LOOP
      EXECUTE (r.constraint_check);
    END LOOP;
  END;
  $$;
SQL


# =>
# PG::ForeignKeyViolation: ERROR:  insert or update on table "friendships" violates foreign key constraint "fk_rails_e3733b59b7" (ActiveRecord::InvalidForeignKey)
# DETAIL:  Key (user_id)=(999) is not present in table "users".

https://github.com/rails/rails/blob/v7.0.3/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L41

---

For sqlite3, run this in the console instead:

ActiveRecord::Base.connection.execute("PRAGMA foreign_key_check")

# => [{"table"=>"friendships", "rowid"=>1, "parent"=>"users", "fkid"=>0}]

https://github.com/rails/rails/blob/v7.0.3/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L214

Venegas

Answer 2

Since I had a lot of fixtures, I went through every fixture and ensured that when it was referencing a fixture, that that fixture being referenced actually existed. Why? For some unrelated reasons, some of my fixtures were referencing other fixtures which didn't exist.

Small example, suppose comments belongs_to a post this will work:

# Posts fixture
one:
  title: MyString
  content: MyText

# Comment fixture
one: 
  comment_content: MyString
  post: one

but this will give the error in the question:

# Posts fixture
one:
  title: MyString
  content: MyText

# Comment fixture
one: 
  comment_content: MyString
  post: somethingrandom

So you have to make sure your 'belongs to' fixtures are pointing to things that actually exist (in the above example, somethingrandom, doesn't exist, so it will give the error).

A small example to better understand the problem

I found it extremely helpful to make a minimal reproducible app as an example, but doing the following:

rails new testapp
cd testapp
rails g scaffold posts title content:text 
rails g scaffold comment comment_content:text post:belongs_to
# Add this to post.rb: has_many :comments, dependent: :destroy
rake db:migrate

At this point rails test should succeed and posts.yml should look like this:

one:
  title: MyString
  content: MyText

two:
  title: MyString
  content: MyText

and comments.yml like this:

one:
  comment_content: MyText
  post: one

two:
  comment_content: MyText
  post: two

Now you can fiddle with the fixtures to reproduce various errors. Suppose we go into comments.yml and change the name of the post that the comment 'one' belongs to:

posts.yml:

one:
  title: MyString
  content: MyText

two:
  title: MyString
  content: MyText

and comments.yml like this:

one:
  comment_content: MyText
  post: one

two:
  comment_content: MyText
  post: twwo # <-- typo!

With the typo, now when we run the tests rake test, we get the error:

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

Related problem

Technically a different problem, but if you leave out the item that a fixture belongs to, you will get

Error:
PostsControllerTest#test_should_create_post:
ActiveRecord::NotNullViolation: PG::NotNullViolation: 
ERROR:  null value in column "post_id" violates not-null constraint

(try leaving out post: one in the above example`).

To address that, systematically work through all model files (and their corresponding fixture files) and ensure every belongs_to association is satisfied:

• Open the first model file in your app
• Open the corresponding fixtures file in your app
• Check that the every belongs_to in the model file has a corresponding item in the fixture.
  • E.g. if a book belongs_to :author, then the book fixture must have author: one (where 'one' is the name of a fixture in the author fixtures).
• Repeat for each model file in the app.

Mctyre

Answer 3

I have the similar problem. I have commented out all my fixtures and received the same error when loading 0 fixtures, so the issue is 100% not with the fixtures data. Looks like rails tries to execute the SQL code to disable foreign keys, which could only be run by superuser. The only effective solution I have found is to add to the test DB user the superuser priviledge. Once done, the error is gone and all fixtures are loaded correctly.

Flaunt

Answer 4

I fixed this issue by simply running the below:

bundle exec rake db:drop RAILS_ENV=test
bundle exec rake db:create RAILS_ENV=test

Inspectorate

Answer 5

My problem was some relations were missing in my fixtures, so I made a small script to detect which were missing

Schedule has_many overrides

Override belongs_to schedule

require 'yaml'

schedules_file = 'test/fixtures/schedules.yml'
overrides_file = 'test/fixtures/overrides.yml'

# Load schedules and overrides fixtures
schedules = YAML.safe_load(File.read(schedules_file), permitted_classes: [Time, Date]) || {}
overrides = YAML.safe_load(File.read(overrides_file), permitted_classes: [Time, Date]) || {}


# Extract schedule names from schedules
schedule_names = schedules.keys

# Check if all override's schedule names are in schedules
overrides.each do |override_name, override_data|
  schedule_name = override_data['schedule']
  unless schedule_names.include?(schedule_name)
    puts "Override #{override_name} has a schedule #{schedule_name} that is not in schedules"
  end
end

Outputs something like

Override o30 has a schedule three_two that is not in schedules
Override O31 has a schedule three_two that is not in schedules
Override 049 has a schedule three_two that is not in schedules
Override o50 has a schedule three_two that is not in schedules
Override o52 has a schedule three_two that is not in schedules
Override o261 has a schedule three_two that is not in schedules
Override o262 has a schedule three_two that is not in schedules

Then fix the relations

Arianaariane