Cannot add foreign key constraint error when running rails migration

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?

Upvotes: 20

Answers (7)

TomDogg

Reputation: 3937

For those who run into this error when adding the index to an EXISTING table that CONTAINS DATA:

Backup the table data (i.e. create a dump file), in order to be able to reverse everything if needed
TRUNCATE the table (empties and resets it completely)
Run the migration again (with the table being empty).

(When the migration wants to add the index and finds records in the table, it will want to add the index data for these records. But it can't because wasn't given the data. So when there are no records present, it will not attempt to do this - thus no error.)

Upvotes: 0

Fralcon

Reputation: 489

For me the problem was that rails was looking up the wrong table for the relationship I was trying to create. I was creating a self referential table and was running this in my migration:

add_reference :categories, :linked_category, foreign_key: true

I wanted the column name to be linked_category_id.

What was happening was that rails was looking for a linked_categories table, which didnt exist:

FOREIGN KEY (`linked_category_id`)
  REFERENCES `linked_categories` (`id`)

The solution was to set which table the foreign key should reference:

add_reference :categories, :linked_category, foreign_key: { to_table: :categories }

Upvotes: 2

toddmetheny

Reputation: 4443

The way I've been handling this since we upgraded to Rails 5.2:

I go ahead and create the migration rails g model Example user:references

Then, before I run the migration, I open the migration file and edit t.references :user, foreign_key: true to t.references :user, foreign_key: true, type: :integer

Then running rake db:migrate should run successfully and add the foreign key for you.

Upvotes: 14

Awol

Reputation: 219

Use this

rails g model Permission user:integer:index privilege:integer:index

Upvotes: 0

jimcgh

Reputation: 5957

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!

Upvotes: 16