Cannot add foreign key constraint error when running rails migration

Question

I have a users table in my MySQL database. In my rails 4 application I created a Permission model as follows:

rails g model Permission user:references privilege:references

When I run the migration for creating the permissions table, the table does get created in the database, but I get the following error:

== 20170223171936 CreatePermissions: migrating ================================
-- create_table(:permissions)
rake aborted!
StandardError: An error has occurred:

Mysql2::Error: Cannot add foreign key constraint: ALTER TABLE `permissions` ADD CONSTRAINT `fk_rails_d9cfa3c257`
FOREIGN KEY (`user_id`)
  REFERENCES `users` (`id`)

Since the users table already exists, I'm puzzled as to why the foreign key creation fails in the permissions table. What could be the problem here?

Answer 1

I fixed this. The column type of users_id in the generated permissions table was int(11), and that of the id column in the users table was int(11) unsigned. TLDR: The data type of the foreign key column and that of the referenced column should be the same.

You can see the column types by running show full columns from <table_name>. You can also see the exact reason why adding the foreign key fails by running show engine innodb status.

I created a migration to remove the unsigned property from the id for the users table, and now that the column types were aligned, the foreign key addition worked!