ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: …

Email this to someoneShare on Google+Share on FacebookTweet about this on TwitterShare on LinkedIn

Problem

You have a Ruby on Rails application/engine with some tests in Minitest and using postgresql as the db. In the original development environment all the tests that use fixtures and foreign key validations pass, but when you try to move to a different environment with new database some or all of you tests fail with a message that refers to the ForeignKeyViolation as in:

Solution

Since active record disables the foreign keys before loading the fixtures in the database, the user that you run the tests with needs to have superuser permissions in the test database. Otherwise the fixtures are not loaded. So add the superuser permissions to your user:

You may need to drop and create your test database.

  • Max Kosenko

    I’ve done as you advised but i’ve got error

    “rake aborted!
    ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ??????: INSERT ??? UPDATE ? ??????? “books” ???????? ??????????? ???????? ????? “fk_rails_53d51ce16a”
    DETAIL: ???? (author_id)=(10) ??????????? ? ??????? “authors”.
    : INSERT INTO “books” (“title”, “description”, “price”, “image”, “quentity_books”, “category_id”, “author_id”, “created_at”, “updated_at”) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING “id”

    all migrations have passed and it (error) displays when i’m trying to seed the test (or development) db

  • kosmasc

    Hi Max,

    Is that for the case described (moving to a different database/environment)?

    What happens when you try to seed your database with a few hardcoded records, without using Faker?

  • Max Kosenko

    I think this problem is somehow connected with PostgresSQL, because when i created this project i used SQLite and all tests passed, but then i’d changed the db adapters like so:

    defaults: &default
    adapter: postgresql
    encoding: unicode
    pool: 5

    development:
    <<: *default
    database: shop_dev

    test:
    <<: *default
    database: shop_tst

    production:
    <<: *default
    database: shop_prod

    and when i run 'rspec' or
    just 'rake db:seed RAILS_ENV=development'
    it displays the error i mentioned in the prev. message

    p.s. Before using postgresql I created the databases (the same as i named them in database.yml) and make my user as superuser and owner of DB, but it seems does not help

  • Max Kosenko

    Do you think it could be a Faker problem? Maybe i should use the ffaker instead?!

  • kosmasc

    Have a look at the link here and the description below:

    http://www.postgresql.org/docs/9.3/static/sql-altertable.html

    section ‘DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER’ –

    “Disabling or enabling internally generated constraint triggers requires superuser privileges”

    In particular, regarding foreign keys:

    ”… internally generated constraint triggers such as those that are used to implement foreign key constraints …”

  • kosmasc

    Try with a couple of hardcoded records (two books, two authors etc) in your seeds file, to see if there is an issue with Faker.

  • Max Kosenko

    It seems the problem is still undetermined. When I try hardcoded records it works, but I cannot seed author_id and category_id — it throws an error of foreign key.
    I’ve even tried to use gem ‘immigrant’ to fill in the missing foreign keys, but i have the same error

  • Max Kosenko

    Thanks a lot!
    I ‘ve solved the problem.
    I added “ActiveRecord::Base.connection.disable_referential_integrity do” at the beginning of seeds.rb, and “end” at the end, so then i did “rake db:reset” -> “rake db:setup” -> “rake db:migrate”

  • kosmasc

    Glad to hear that Max!

  • Constantin Tofan

    I am glad to hear that it solved your issues. However, having the same problem I did exactly what you did and still doesn’t work. Same errors.

    Do you have any other solution to this problem?
    I hope to hear from you soon.
    Cheers,
    Constantin