Add a reference column migration in Rails 4
Solution 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.
Solution 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
Solution 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
Solution 4
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
Solution 5
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
Comments
-
Don P almost 2 years
A user has many uploads. I want to add a column to the
uploads
table that references theuser
. 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?
-
poerror about 9 yearsIn many cases it's good to add foreign key as well. add_foreign_key (Rails 4.2)
-
Taufiq Muhammadi about 9 yearsIsn't the convention to use plurals as the table name in migration? AddForeignKeyToUploads ?
-
Kirti Thorat about 9 years@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 :)
-
the911s almost 9 yearsIs 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 separateadd_index
line (where it's possible to specifyname: "index_name"
) -
user1801879 almost 9 yearsI believe you can do all in one line: add_reference :uploads, :user, index: true, foreign_key: true @KirtiThorat
-
jrhorn424 almost 9 yearsNow, 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
producesadd_reference :uploads, :user, index: true, foreign_key: true
in the appropriate migration. -
Mike Bethany over 8 yearsThis 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.
-
Washington Botelho over 8 yearsUse
...index: true, foreign_key: true
instead o lineadd_foreign_key
. -
akashchandrakar about 8 yearsIs there and similar special generator syntax for migrations to drop foreign key in rails 4.2?
-
Christian Fazzini about 8 years@aksam
remove_reference :uploads, :user
would remove the associated column and index. You can verify this by checkingschema.rb
-
ryanttb almost 8 yearsRails 4.2.3 appears to do this in one line by default.
rails g migration AddUserToUploads user:references
created a migration withadd_reference :uploads, :user, index: true, foreign_key: true
for me. -
Ka Mok over 7 yearsFor name-spaced classes, like
Local::User
instead ofUser
do something likerails g migration AddLocalUserToUploads user:references
. -
Saravanabalagi Ramachandran over 7 yearsdoes this automatically add
:index
-
Mirror318 over 7 years@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
-
Saravanabalagi Ramachandran over 7 yearsyeah, i got an "index exists" error when i manually added the add_index in migration :P @Mirror318
-
tobyc over 7 yearsThis is some next level rails magic. Been doing rails for years and didn't realise the migration name actually meant anything.
-
Wit almost 7 yearsWe should also add
belongs_to :user
inUpload
class, so we can useupload.user
to get the user instance. -
geoboy over 6 years@Mirror318 What if you already add have a
user_id
column onuploads
? Will this error out or just update the column fromt.integer
tot.references
? -
geoboy over 6 years@KirtiThorat What happens if there is already
user_id
column in theuploads
table - willreferences
fail or ignore? -
Mirror318 over 6 years@geoboy I'd personally much rather remove that column and add
t.references
, or if you have data you want to keep, renameuser_id
to something temporary, createt.references
, copy the data over, then remove the temp column -
geoboy over 6 yearsWhy do we need both
foreign_key
andt.reference
? Isn'tt.reference
essentially equivalent toforiegn_key
+index
? -
geoboy over 6 years@Mirror318 Thanks, could you elaborate more why? What does
t.reference
tells ActiveRecord which is different fromt.foreign_key
and anindex
separately? -
Mirror318 over 6 years@geoboy 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.
-
Afolabi Olaoluwa over 6 yearsCan 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.
-
Afolabi Olaoluwa over 6 years@KaMok seems your comment looks like what I have. But PG relation error is what I get.
-
Mirror318 over 6 years@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
-
M. Habib about 6 yearsThis answer seems to be duplicate of @Mirror318's answer. Please comment on above answer if you think something is missing from it. Thanks.
-
vinibrsl about 6 yearsUpdate to Rails 5: it generates the migration with the FK as a hash parameter
add_reference :merchants, :merchant_storefront, foreign_key: true
-
hadees about 5 yearsIt's also a lot clearer what's happening. But yeah UUID should be standard now.
-
javierojeda almost 4 yearsAlso for rails 5 onwards please refer to @Mirror318 answer: stackoverflow.com/a/39502978/4376810
-
João Ramires over 3 yearsHow 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?
-
Clint Clinton over 3 yearsIf 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