Debugging ActiveRecord fixture associations

You may have encountered this unhelpful error when loading fixtures with an error in the model associations. Here's how to find the table that's causing the issue.

When you're loading fixtures and you have incorrect model associations or outdated fixtures, you may encounter this error:

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

Normally, the way to fix this would be to meticulously review your associations and fixture files to see if there are any references to outdated information such as columns or references. But what happens if you've done this and still can't find the issue? You can try loading the fixtures individually and use good ol' SQL to find the offending table(s).

Loading Fixtures Individually

Normally if you run rails db:fixtures:load it will load all of the test fixtures into your database. However, you can load these fixtures individually as well. Open up rails console and run the following commands to load fixtures for a single model:

require 'active_record/fixtures'
ActiveRecord::FixtureSet.create_fixtures('test/fixtures', 'images')

Checking Constraints in the Database Console

In the example above, we are loading fixtures from the images.yml file. The purpose of this exercise is to find the SQL that Rails generates when loading the fixture file. You can see this below:

do $$
  declare r record;
  BEGIN
    FOR r IN (
      SELECT FORMAT(
        'UPDATE pg_constraint SET convalidated=false WHERE conname = ''%I'' AND connamespace::regnamespace = ''%I''::regnamespace; ALTER TABLE %I.%I VALIDATE CONSTRAINT %I;',
        constraint_name,
        table_schema,
        table_schema,
        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;
$$;

Here you can see that Rails is trying to validate the foreign key constraints. If you have a foreign key constraint that is invalid, this will cause the error we saw earlier. You can run this SQL in your database console to see which table is causing the issue. I'm using PostgreSQL so I can run this SQL command there to see what I get:

DETAIL: Key (image_id)=(980190962) is not present in table "images". CONTEXT: SQL statement "UPDATE pg_constraint SET convalidated=false WHERE conname = 'fk_rails_25fd743944' AND connamespace::regnamespace = 'public'::regnamespace; ALTER TABLE public.product_images VALIDATE CONSTRAINT fk_rails_25fd743944;"

Progress! We can see that there is an error that occurs from an invalid association in the images and product_images table. Upon inspection, I discovered that this error was due to a hardcoded ID I had left in the fixture file when I was testing the associations.

I hope this little tip helps someone out there as it was an error message that was frustrating due to its unclear nature.