Rails: Remove foreign key constraint

Question

I have following association with user and semester. And I created user table with semester id as foreign key. So user is not created if semester id not present. But semester id is optional in the registration form.

class User < ApplicationRecord
  belongs_to :semester
end
class Semester < ApplicationRecord
  has_many :users
end
    class CreateUsers < ActiveRecord::Migration[5.1]
      def change
        create_table :users do |t|
          t.string :email
          t.references :semester, foreign_key: true
          t.timestamps
        end
      end
    end

So how can I create another migration to drop the foreign key constraint? So in the user table I there should be two columns email and semester_id but semester_id should not have foreign key constraint because it is an optional field.

score 17 · Answer 1 · answered Mar 06 '18 at 09:48

17

class RemoveSemestersFKFromUsers < ActiveRecord::Migration[5.1]
  def change
    if foreign_key_exists?(:users, :semesters)
      remove_foreign_key :users, :semesters
    end
  end
end

Remember to set the association as optional: true to remove the presence validation as well.

ActiveRecord::ConnectionAdapters::SchemaStatements

answered Mar 06 '18 at 09:48

max

96,212
14
104
165

2

You might need to do `change_column :users, :semester_id, :integer, null: true` if the column is not nullable as well. – max Mar 06 '18 at 09:51
what about rolling back this migration? it will not create foreign_key back – Роман Соляник Feb 08 '22 at 15:45
... it will not create foreign_key back because of 'foreign_key_exists?' condition.. without this condition it works fine – Роман Соляник Feb 08 '22 at 15:58

score 0 · Answer 2 · answered Jun 23 '20 at 15:29

I experienced this same issue when working on a Rails 6 application.

Here's how I solved it:

I had a Users table and a Roles table. I wanted the Users table to belong to the Roles table, that I wanted to add a reference of the Roles table to the Users table. I also had an Admin and Student Models that inherit from the Users table

Firstly, create a migration to add a reference of the Roles table to the Users table:

rails generate migration AddRoleRefToUsers role:references

This will create a migration file that contains the following:

class AddRoleRefToUsers < ActiveRecord::Migration[6.0]
  def change
    add_reference :users, :role, null: false, foreign_key: true
  end
end

Simply change null: false to null: true. So that we will have;

class AddRoleRefToUsers < ActiveRecord::Migration[6.0]
  def change
    add_reference :users, :role, null: true, foreign_key: true
  end
end

And then migrate the database:

rails db:migrate

Finally, check your user model:

It will contain the following:

class User < ApplicationRecord
  belongs_to :role
end

Simply add optional: true to the belongs_to association. So that we will have:

class User < ApplicationRecord
  belongs_to :role, optional: true
end

That's all.

I hope this helps

Ben · Answer 3 · 2018-03-06T09:45:05.017

-1

Make the reference optional in your model :

class User < ApplicationRecord
  belongs_to :semester, optional: true
end

See here, 4.1.2.11

edited Mar 06 '18 at 09:45

answered Mar 06 '18 at 09:26

Ben

5,030
6
53
94

1

Not a good idea since foreign keys are not nullable in most DBs. That just removes the application level validation. – max Mar 06 '18 at 09:43
What would you advise ? – Ben Mar 06 '18 at 09:45
2

`remove_foreign_key` obviously. – max Mar 06 '18 at 09:49

Rails: Remove foreign key constraint

3 Answers3