Cannot add foreign key constraint error when running rails migration

rails migration add constraint
rails migration foreign key
rails migration references
rails foreign key
failed to open the referenced table
rake db:migrate
rails run migration
rails run specific 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?

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!

New reference to old table fails on foreign key (MySQL bigint(20) v , rails db:migrate == 20170927000118 CreateRole: migrating Original message: Mysql2::Error: Cannot add foreign key constraint: ALTER The new table was using InnoDB and the reference table was using MyISAM. ERROR 1215 (HY000): Cannot add foreign key constraint But MySQL never tells you exactly WHY it failed. There’s actually a multitude of reasons this can happen.

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.

Cannot add foreign key constraint: CREATE TABLE, I'm using MySQL and Rails 4. gem 'friendly_id', '~> 5.0.0' all later migrations canceled: Mysql2::Error: Cannot add foreign key constraint:� Rails 4.2 newly supports adding and removing foreign keys (in migrations), like: # add a foreign key to `articles.author_id` referencing `authors.id` add_foreign_key :articles, :authors What I don't understand is: How is this. add_foreign_key :articles, :authors different from this: add_column :articles, :author_id, :integer

I often run into this problem because there is a missing index on the column targeted by the foreign key.

Cannot add foreign key constraint error when running rails migration, Cannot add foreign key constraint error when running rails migration. 312. February 24, 2017, at 01:40 AM. I have a users table in my MySQL database. So, I have a habtm join table campaign_leads that has a foreign key to the campaigns table. Then the auto schema maintainer tries to drop campaigns so it can recreate it, but postgresql doesn't allow that because of the foreign key.

Use this

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

rails foreign key issue, unable to migrate : rails, Hi, As much as I like Rails, I'm not very proficient at databases/schemas and migrations. StandardError: An error has occurred, all later migrations canceled: Mysql2::Error: Can't create TABLE `articles` ADD CONSTRAINT ` fk_rails_e74ce85cbc` FOREIGN KEY They're doing well as a business, and they're using Rails. Now, if you use the special generator syntax for migrations, Rails 4.2 will automatically create the correct migration with foreign key constraints included. rails g migration AddUserToUploads user:references produces add_reference :uploads, :user, index: true, foreign_key: true in the appropriate migration. – jrhorn424 Jul 27 '15 at 22:49

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 }

MySQL Error Code 1215: "Cannot add foreign key constraint , How to diagnose: Run SHOW TABLES or SHOW CREATE TABLE for each of the parent tables. If you get error 1146 for any of them, it means� There is however a trade-off: db/schema.rb cannot express database specific items such as foreign key constraints, triggers, or stored procedures. While in a migration you can execute custom SQL statements, the schema dumper cannot reconstitute those statements from the database.

Dealing With MySQL Error Code 1215: ''Cannot Add Foreign Key , ''Cannot add foreign key constraint'' is a common MySQL error. How to diagnose: Run SHOW TABLES or SHOW CREATE TABLE for each of� Before renaming a table, you should verify that any foreign key constraints on the table have an explicit name in your migration files instead of letting Laravel assign a convention based name. Otherwise, the foreign key constraint name will refer to the old table name.

Active Record Migrations — Ruby on Rails Guides, There are certain queries that can't run inside a transaction. While it's not required you might want to add foreign key constraints to If someone tries to revert your migration, an error message will be displayed saying that it can't be done. 2 Creating a Migration 2.1 Creating a Standalone Migration. Migrations are stored as files in the db/migrate directory, one for each migration class. The name of the file is of the form YYYYMMDDHHMMSS_create_products.rb, that is to say a UTC timestamp identifying the migration followed by an underscore followed by the name of the migration.

Migrations, Active Record tracks which migrations have already been run so all you have to to your database (for example create a foreign key constraint) then the execute your migration an error message will be displayed saying that it can't be done. Why GitHub? Features →. Code review; Project management; Integrations; Actions; Packages; Security

Comments
  • Experienced a similar problem with Rails 5.1, where primary keys are now BigInts. I added a foreign key with add_column(:projects, :price_list_id, :integer, null: false) and when I tried to add the foreign key the migration bombed. I needed add_column(:projects, :price_list_id, :bigint, null: false) for the foreign key so that they matched.
  • I kept running into this error over and over. Sure, some of the fixes above worked, but having this error keep popping up, I decided to give this one a shot. Low and behold, it worked!