CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Noel Frostpaw
Noel Frostpaw

Reputation: 4009

Ruby on Rails: db:schema:load fails due foreign key

okay,

I've been trying to wrap my head around this problem for a while, and I simply cannot come up with an explanation why this behavior is happening. I have a project that's running Ruby 2.7, Ruby on Rails 5.2.8.1.

On a new system, I'm trying to set up the database using a MySQL 5.7 database, and every time I run bundle exec rake db:create db:schema:load it fails because of a Foreign Key error. And the weird part is that it tries to create the Foreign Key before the actual tables, which is what doesn't make any sense to me as the add_foreign_key statement is the last statement in the schema.rb.

Simplified schema.rb

ActiveRecord::Schema.define(version: 20211230212616) do

  create_table "categories", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci" do |t|
    t.integer "category_id", null: false
    t.string "subtitle"
    t.text "page_content"
    t.text "pinned_styles"
    t.text "unpinned_styles"
    t.text "updated_by"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["category_id"], name: "index_categories_on_category_id", unique: true
  end

  create_table "faqs", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci" do |t|
    t.text "question"
    t.text "answer"
    t.bigint "category_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["category_id"], name: "index_faqs_on_category_id"
  end

  add_foreign_key "faqs", "categories"
end

Error Message

bundle exec db:drop db:create db:schema:load

-- create_table("categories", {:force=>:cascade, :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"})
rake aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails: DROP TABLE IF EXISTS `categories` CASCADE
/Users/olivar/RubymineProjects/catalog/db/schema.rb:15:in `block in <main>'
/Users/olivar/RubymineProjects/catalog/db/schema.rb:13:in `<main>'
/Users/olivar/.rbenv/versions/2.7.2/bin/bundle:25:in `load'
/Users/olivar/.rbenv/versions/2.7.2/bin/bundle:25:in `<main>'

Caused by:
Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails
/Users/olivar/RubymineProjects/catalog/db/schema.rb:15:in `block in <main>'
/Users/olivar/RubymineProjects/catalog/db/schema.rb:13:in `<main>'
/Users/olivar/.rbenv/versions/2.7.2/bin/bundle:25:in `load'
/Users/olivar/.rbenv/versions/2.7.2/bin/bundle:25:in `<main>'
Tasks: TOP => db:schema:load

Yet, every time the command fails because it creates the categories table, then it tries the foreign key, without the faqs table existing (or switches the tables).

Can someone explain this behavior?

Upvotes: 3

Views: 607

Answers (1)

Mike Pollitt
Mike Pollitt

Reputation: 2007

Not sure if this helps, but I ran into a similar error because I had inadvertently set my development and test environment databases to the same name.

The default behaviour of rails db:create and rails db:schema:load is to perform identical actions on both the development and test databases, as defined in /config/database.yml.

I was getting this sequence of events:

➜ rails db:create
I, [2025-01-24T16:38:19.755395 #66667]  INFO -- : [dotenv] Loaded .env
I, [2025-01-24T16:38:19.755429 #66667]  INFO -- : [dotenv] Loaded .env
Created database 'icp_development'
Database 'icp_development' already exists
➜  rails db:schema:load
I, [2025-01-24T16:38:26.121901 #66695]  INFO -- : [dotenv] Loaded .env
I, [2025-01-24T16:38:26.121935 #66695]  INFO -- : [dotenv] Loaded .env
bin/rails aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Cannot drop table 'organisations' referenced by a foreign key constraint 'fk_rails_d8aeb44d68' on table 'sites'. (ActiveRecord::StatementInvalid)

I was scratching my head, thinking, "How can I be getting a FK conflict before I even finish creating the database?" Then I realised that via my ENV var substitution, I was inadvertently setting the same DB name for both development and test environments. Once I fixed that, I got this instead:

➜  rails db:reset
I, [2025-01-24T16:47:37.284686 #67922]  INFO -- : [dotenv] Loaded .env
I, [2025-01-24T16:47:37.284719 #67922]  INFO -- : [dotenv] Loaded .env
Dropped database 'icp_development'
Database 'icp_test' does not exist
Created database 'icp_development'
Created database 'icp_test'

Upvotes: 0

Related Questions