add_column for references (Rails)

Question

I have the following Rails migration which works perfectly (irrelevant pieces removed):

create_table :comments do |t|
  t.text :body
  t.references :post
end

Now I'd like to add an author column to my comments table (which is the userid of a user), but I have no idea how to do it (I'm tempted to just write the MySql-specific syntax using an execute).

I've been looking at add_column here which doesn't mention references. I've actually found TableDefinition#references but I have no idea how to use it with an add_column statement.

Is this possible? Also, is it true that, for MySql, the "references" functionality does not actually establish relationships between the tables?

score 116 · Accepted Answer · answered Oct 15 '10 at 18:21

116

While it's too late to get any points out of this, I thought I'd post the best way for posterity :)

use change_table instead of create_table to add columns to a table that already exists, with all the TableDefinition goodness:

self.up do
  change_table :comments do |t|
    t.references :author
  end
end

This might seem trivial, but other gems like Devise make heavy use of their own custom table definitions, and this way you can still use them.

answered Oct 15 '10 at 18:21

Jaime Bellmyer

23,051
7
53
50

Okay, so I add an author field to the comments table and I use references. Does that make a difference in MySql? Do I still need to change the model? – Dan Rosenstark Oct 15 '10 at 22:11
Yes, you'd still need to add "has_many :comments" in your author.rb, and "belongs_to :author" in your comment.rb. The migration code only creates fields in the database, which are useless until you call the right ActiveRecord methods I've listed here. – Jaime Bellmyer Oct 16 '10 at 07:39
Is this still the best way in rails3? Also, this should really be marked the answer... – docwhat Oct 18 '10 at 18:53
Yeah, this is the best way. In fact, I just used it in a rails 3 app this weekend (during Rails Rumble). And I'm almost 2 years late to be marked the right answer - this is an old question! – Jaime Bellmyer Oct 19 '10 at 01:58
@Jaime Bellmyer if you use @yar in your comment and you say why this answer beats the best answer, I'll mark it. – Dan Rosenstark Oct 19 '10 at 17:05
@yar - this is the best answer because the user was asking how to get table definitions like `t.references`, which you can't do with add_column. The Devise gem implements its own custom table definitions, so you don't have to fill your migrations with dozens of tough-to-remember field names. Hopefully, this practice will become more popular with gems and plugins in rails. – Jaime Bellmyer Oct 19 '10 at 22:39
@Jaime Bellmyer, okay +1 and best answer and I've also learned something. I'll have to check out the Devise gem, too, I guess. Thanks! – Dan Rosenstark Oct 19 '10 at 23:44
1

@Jamie Bellmyer What would be the best way to do the reverse migration? I like to keep my migrations clean both ways. – Jackson Miller Feb 07 '11 at 18:16
4

@Jackson Miller there isn't a t.unreference to undo your reference addition, but you can use the long way with t.remove :author_id in the example above. If you used "t.references :author, :polymorphic => true" then you'll also need to have "t.remove author_type" in your down migration. – Jaime Bellmyer Mar 15 '11 at 23:39
4

Unfortunately, this answer is 4-5 years old. Even though this can work, this is no longer the acceptable method for Rails 4+. Please see the DRY, one-liner suggestion by Rajeev Kannav Sharma & Josh Crozier – Eric Wanchic Jan 28 '15 at 19:37

score 102 · Answer 2 · edited Jan 07 '14 at 19:42

102

add_reference :table_name, :reference, index: true

edited Jan 07 '14 at 19:42

Josh Crozier

233,099
56
391
304

answered Jan 07 '14 at 19:25

Rajeev Sharma

1,527
2
13
16

3

This is the good answer, at less for _Rails 4_. Here the [API reference](http://apidock.com/rails/v4.0.2/ActiveRecord/ConnectionAdapters/SchemaStatements/add_reference) – fguillen Sep 08 '14 at 15:42
3

Agreed. This should now be the 'new' correct answer for Rails 4+. – Eric Wanchic Jan 28 '15 at 19:34
2

You also can pass `add_reference :table_name, :reference, polymorphic: true, index: true` – Scudelletti May 05 '15 at 15:43
For my business, I did it as `add_reference :users, :tenant, foreign_key: true`. – Lane Aug 24 '21 at 08:10

score 38 · Answer 3 · edited Oct 31 '14 at 20:06

38

Finally got it

add_column :locations, :state_id , :integer, :references => "states"

edited Oct 31 '14 at 20:06

KARASZI István

30,900
8
101
128

answered Apr 21 '11 at 10:12

Swapnil Chincholkar

2,869
3
22
22

Chickoklkar, that's great. If you could please delete your other answer, and also if you could link to the Rails docs, that would great. Thanks! – Dan Rosenstark Apr 21 '11 at 23:27
Does this actually do anything special? I can't find any documentation that says that add_column will actually do anything with the :references symbol in options. – Ibrahim Apr 25 '13 at 22:05
1

This was probably correct answer in 2012, but with Rails 4 it isthe answer by @rajeev-kannav-sharma. – zmilojko Oct 13 '14 at 07:42

score 22 · Answer 4 · edited Feb 12 '11 at 22:01

22

First, do:

script/generate migration AddAuthorIdToComments

Open the generated file and add this line:

add_column :comments, :author_id, :integer

Then in your model files:

class User < ActiveRecord::Base
  has_many :comments, :foreign_key => "author_id"
end

class Comment
  belongs_to :author, :class_name => User
end

edited Feb 12 '11 at 22:01

True Soft

8,675
6
54
83

answered Jan 29 '09 at 22:39

Milan Novota

15,506
7
54
62

very nice, thanks for that. Of course adding the column as an integer is not what I was hoping for, but since that's what the original migration does anyway... THANKS for outlining how to setup the relationships in the model classes. – Dan Rosenstark Jan 29 '09 at 22:51
Simple truth is, you just can't use references in this scenario, because is only available for table definitions. And yes, as Craig says, migrations don't care about setting foreign keys in database. – Milan Novota Jan 29 '09 at 23:16
Okay, I get it re: table definitions: at the column level it's not available (only at the table level). A question: if the column in User is called "id", it works, right? "author_id" is the name of the new column in comments ONLY, right? – Dan Rosenstark Jan 29 '09 at 23:43
Yes, the connection between those two is defined on the model level (see foreign_key, class_name). – Milan Novota Jan 30 '09 at 01:31
What does the "class_name => User" do? – AnApprentice Sep 14 '10 at 05:33
Only for the protocol. The classname must every time in "". So the right syntax is belongs_to :author, :class_name => "User" and not belongs_to :author, :class_name => User – ThreeFingerMark Jan 21 '11 at 09:35

score 2 · Answer 5 · answered Jan 29 '09 at 22:43

2

It's been a while since I've looked at this, but last I checked migrations don't support creating foreign keys. Fortunately, however, there is a plug-in for it. I've used this and it works well.

answered Jan 29 '09 at 22:43

Craig Stuntz

125,891
12
252
273

score 0 · Answer 6 · edited Jun 14 '12 at 08:15

0

You could add the column by add_column(:table, :column_name, :type, :options) in a new Migration.

edited Jun 14 '12 at 08:15

KARASZI István

30,900
8
101
128

answered Jan 29 '09 at 22:34

Thanks Mike. What would the :type and :options be? – Dan Rosenstark Jan 29 '09 at 22:36

add_column for references (Rails)

6 Answers6

Linked