Rails model generation fails due to foreign key type

Question

I created a model with the following command:

rails g model UserCertification user:references certification:references certification_no:string

which is referencing to my devise user model user:references.

On the db:migrate I receive the following error: Caused by:

ActiveRecord::MismatchedForeignKey: Column user_id on table user_certifications does not match column id on users, which has type bigint(20). To resolve this issue, change the type of the user_id column on user_certifications to be :bigint. (For example t.bigint :user_id). Original message: Mysql2::Error: Cannot add foreign key constraint

Here is my migration

class CreateUserCertifications < ActiveRecord::Migration[6.0]
  def change
    create_table :user_certifications do |t|
      t.references :user, null: false, foreign_key: true
      t.references :certification, null: false, foreign_key: true
      t.string :certification_no

      t.timestamps
    end
  end
end

I tried to set the type to integer with t.references :user, null: false, foreign_key: true,type: :integer

I also deleted the model and redid and checked with previous references, without success. Perhaps someone has an idea?

Answer 1

Try setting the type in the reference to match the other table:

t.references(:user, null: false, type: :bigint)

If that doesn't work, try creating the foreign key in user_certifications explicitly:

t.bigint :user_id, null: false
t.references(:user)

Answer 2

Adding more than one reference?

To expand on the accepted answer:

t.references(:user, null: false, type: :bigint)

...If you are trying to add more than one reference, you may find that it still fails - but the console error will still direct you to the first error.

e.g. in my case I was adding four references

t.references(:user, null: false, type: :bigint)
t.references(:blog, null: false, type: :integer)
t.references(:post, null: false, type: :bigint)
t.references(:comment, null: false, type: :bigint)

OR

t.references :user, null: false, type: :bigint
t.references :blog, null: false, type: :integer
t.references :post, null: false, type: :bigint
t.references :comment, null: false, type: :bigint

Changing the first only, the console told me the first (to specify type) did not work, and gave the same error as not specifying type.

Changing all four references succeeded, which can only lead me to conclude that the console error was triggered by the second reference but showed the first reference still to be the rogue, rather than the offending one (the as-yet-unchanged second reference).

So, if you have more than one reference, change them all to match the solution, (rather than expecting the console to tell you that your edited t.references has succeeded and that your second had failed.)

Answer 3

If you see this message in Rails 6 (and likely also other versions), the error message may be misleading and have nothing to do with the data type at all.

For instance, given your example, if the table certifications does not exist (e.g. becaue it's spelled wrong), you will still get the error message claiming that user_id should be bigint, even if the user reference is perfectly fine.

Systematically comment out some of your references and run db:migrate and db:rollback until you have identified the reference that is actually failing. Then double-check if it is correct.

Answer 4

I was having this issue due to a very confusing mismatched foreign keys reporting bug in Rails 6,

Basically with MySQL, currently Rails can report this error for the wrong column in the table.

There is a fix in the works, so should be resolved in the near future.

If you have this issue, you would need to examine all references in your create_table statement, and see if any have a potential datatype issue - taking into account the new default for Rails 6 is bigint, and but older tables may be using int

Answer 5

One would assume that specifying a type is not necessary if both migrations were generated with Rails 6.0 because :bigint is already the default type for the :id column. In my case specifying the type did not make Rails happy. Adding a second migration that only adds the references did.

class AddReferencesToUserCertifications < ActiveRecord::Migration[6.0]
  def change
    add_reference :user_certifications, :user, null: false, foreign_key: true
    add_reference :user_certifications, :certification, null: false, foreign_key: true
  end
end