Add a reference column migration in Rails 4

Question

A user has many uploads. I want to add a column to the uploads table that references the user. What should the migration look like?

Here is what I have. I'm not sure if I should use (1) :user_id, :int or (2) :user, :references. I'm not even sure if (2) works. Just trying to do this the "rails" way.

class AddUserToUploads < ActiveRecord::Migration
  def change
    add_column :uploads, :user_id, :integer
  end
end

Relevant question except for Rails 3. Rails 3 migrations: Adding reference column?

Meant

Answer 1

Rails 4.x

When you already have users and uploads tables and wish to add a new relationship between them.

All you need to do is: just generate a migration using the following command:

rails g migration AddUserToUploads user:references

Which will create a migration file as:

class AddUserToUploads < ActiveRecord::Migration
  def change
    add_reference :uploads, :user, index: true
  end
end

Then, run the migration using rake db:migrate. This migration will take care of adding a new column named user_id to uploads table (referencing id column in users table), PLUS it will also add an index on the new column.

UPDATE [For Rails 4.2]

Rails can’t be trusted to maintain referential integrity; relational databases come to our rescue here. What that means is that we can add foreign key constraints at the database level itself and ensure that database would reject any operation that violates this set referential integrity. As @infoget commented, Rails 4.2 ships with native support for foreign keys(referential integrity). It's not required but you might want to add foreign key(as it's very useful) to the reference that we created above.

To add foreign key to an existing reference, create a new migration to add a foreign key:

class AddForeignKeyToUploads < ActiveRecord::Migration
  def change
    add_foreign_key :uploads, :users
  end
end

To create a completely brand new reference with a foreign key(in Rails 4.2), generate a migration using the following command:

rails g migration AddUserToUploads user:references

which will create a migration file as:

class AddUserToUploads < ActiveRecord::Migration
  def change
    add_reference :uploads, :user, index: true
    add_foreign_key :uploads, :users
  end
end

This will add a new foreign key to the user_id column of the uploads table. The key references the id column in users table.

NOTE: This is in addition to adding a reference so you still need to create a reference first then foreign key (you can choose to create a foreign key in the same migration or a separate migration file). Active Record only supports single column foreign keys and currently only mysql, mysql2 and PostgreSQL adapters are supported. Don't try this with other adapters like sqlite3, etc. Refer to Rails Guides: Foreign Keys for your reference.

Saponaceous

In many cases it's good to add foreign key as well. add_foreign_key (Rails 4.2) – Engstrom

Isn't the convention to use plurals as the table name in migration? AddForeignKeyToUploads ? – Tiffaneytiffani

@TaufiqMuhammadi Both singular & plural form work in this case. Rails takes care of detecting and normalizing the table name. I do agree that table name in pluralized form would be more intuitive here, even though both should work. Hence, the update. Thank you for your comment :) – Saponaceous

Is it possible to override the index name (e.g. when the auto-generated one is too long) inline with a polymorphic t.references line w/ index: true?. The inline declaration is nice syntax sugar compared to to having a separate add_index line (where it's possible to specify name: "index_name") – Christmann

I believe you can do all in one line: add_reference :uploads, :user, index: true, foreign_key: true @KirtiThorat – Mistymisunderstand

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. – Ululant

This is the correct way to do it but it's vital to understand that if you have to recreate your database it will fail due to the foreign key constraint. For instance if you backup your production database and then try to restore it on your development machine it will fail. – Lath

Use ...index: true, foreign_key: true instead o line add_foreign_key. – Lammers

Is there and similar special generator syntax for migrations to drop foreign key in rails 4.2? – Urbanism

@aksam remove_reference :uploads, :user would remove the associated column and index. You can verify this by checking schema.rb – Polytypic

Rails 4.2.3 appears to do this in one line by default. rails g migration AddUserToUploads user:references created a migration with add_reference :uploads, :user, index: true, foreign_key: true for me. – Supramolecular

@KirtiThorat What happens if there is already user_id column in the uploads table - will references fail or ignore? – Cerography

Why do we need both foreign_key and t.reference? Isn't t.reference essentially equivalent to foriegn_key + index? – Cerography

Update to Rails 5: it generates the migration with the FK as a hash parameter add_reference :merchants, :merchant_storefront, foreign_key: true – Demonize

Also for rails 5 onwards please refer to @Mirror318 answer: https://mcmap.net/q/92268/-add-a-reference-column-migration-in-rails-4 – Acie

Answer 2

Rails 5

You can still use this command to create the migration:

rails g migration AddUserToUploads user:references

The migration looks a bit different to before, but still works:

class AddUserToUploads < ActiveRecord::Migration[5.0]
  def change
    add_reference :uploads, :user, foreign_key: true
  end
end

Note that it's :user, not :user_id

Akbar

For name-spaced classes, like Local::User instead of User do something like rails g migration AddLocalUserToUploads user:references. – Eipper

does this automatically add :index – Soft

@Zeke Yes, run the migration and check your schema, it should say something like t.index ["user_id"], name: "index_uploads_on_user_id", using: :btree – Akbar

yeah, i got an "index exists" error when i manually added the add_index in migration :P @Akbar – Soft

This is some next level rails magic. Been doing rails for years and didn't realise the migration name actually meant anything. – Hagiology

We should also add belongs_to :user in Upload class, so we can use upload.user to get the user instance. – Eisegesis

@Akbar What if you already add have a user_id column on uploads? Will this error out or just update the column from t.integer to t.references? – Cerography

@Cerography I'd personally much rather remove that column and add t.references, or if you have data you want to keep, rename user_id to something temporary, create t.references, copy the data over, then remove the temp column – Akbar

@Akbar Thanks, could you elaborate more why? What does t.reference tells ActiveRecord which is different from t.foreign_key and an index separately? – Cerography

@Cerography I don't know the exact differences (someone else might know...?), but this is a general case of "The Rails Way™", where the less manual work/customization, the better. – Akbar

Can somebody help me out here? Am having an error in a case of a modular rails app engine based. When I generate this migration and run the migration, I get relation “users” does not exist. A using a mountable rails engine as a result of modularity. – Bitters

@KaMok seems your comment looks like what I have. But PG relation error is what I get. – Bitters

@AfolabiOlaoluwaAkinwumi engines are a pain, I think when I was doing something with them I had to name space them e.g. my_engine_users – Akbar

Answer 3

if you like another alternate approach with up and down method try this:

  def up
    change_table :uploads do |t|
      t.references :user, index: true
    end
  end

  def down
    change_table :uploads do |t|
      t.remove_references :user, index: true
    end
  end

Breeze

Answer 4

Create a migration file

rails generate migration add_references_to_uploads user:references

Default foreign key name

This would create a user_id column in uploads table as a foreign key

class AddReferencesToUploads < ActiveRecord::Migration[5.2]
  def change
    add_reference :uploads, :user, foreign_key: true
  end
end

user model:

class User < ApplicationRecord
  has_many :uploads
end

upload model:

class Upload < ApplicationRecord
  belongs_to :user
end

Customize foreign key name:

add_reference :uploads, :author, references: :user, foreign_key: true

This would create an author_id column in the uploads tables as the foreign key.

user model:

class User < ApplicationRecord
  has_many :uploads, foreign_key: 'author_id'
end

upload model:

class Upload < ApplicationRecord
  belongs_to :user
end

Edacity

How could I specify a field to be used as foreign key? Lets say I want to use as foreign key a unique string field instead of the row id. How's the migration syntax for that? – Decontrol

If I understood correctly, you want to use a field other than the row id. add_foreign_key :from_table, :to_table, column: :field primary_key: "row_id" You might also want to index it, for performance reasons – Edacity

Answer 5

Just to document if someone has the same problem...

In my situation I've been using :uuid fields, and the above answers does not work to my case, because rails 5 are creating a column using :bigint instead :uuid:

add_reference :uploads, :user, index: true, type: :uuid

Reference: Active Record Postgresql UUID

Digestant

It's also a lot clearer what's happening. But yeah UUID should be standard now. – Outcross

Answer 6

[Using Rails 5]

Generate migration:

rails generate migration add_user_reference_to_uploads user:references

This will create the migration file:

class AddUserReferenceToUploads < ActiveRecord::Migration[5.1]
  def change
    add_reference :uploads, :user, foreign_key: true
  end
end

Now if you observe the schema file, you will see that the uploads table contains a new field. Something like: t.bigint "user_id" or t.integer "user_id".

Migrate database:

rails db:migrate

Hartwell

This answer seems to be duplicate of @Mirror318's answer. Please comment on above answer if you think something is missing from it. Thanks. – Dibromide

Answer 7

Another syntax of doing the same thing is:

rails g migration AddUserToUpload user:belongs_to

Samhita

Answer 8

class MigrationName < ActiveRecord::Migration[7.0]
  disable_ddl_transaction!
  
  def change
    add_reference :uploads, :user, index: {algorithm: :concurrently}
  end
end

Cailean

I guess you're adding an answer with Rails7-specific details. Would it make sense to add a word of explanation around what's new? – Bili

Rails adds an index non-concurrently to references by default, which blocks writes in Postgres. Make sure the index is added concurrently. – Cailean