Specifying column name in a "references" migration

Question

I want to make a migration in Rails, referencing another table. Usually, I would do something like:

add_column :post, :user, :references

This creates a column named user_id in posts table. But what if, instead of user_id, I want something like author_id? How can I do that?

Sheharyar · Answer 1 · 2017-03-26T22:35:32.757

337

For Rails 5+

Initial Definition:

If you are defining your Post model table, you can set references, index and foreign_key in one line:

t.references :author, index: true, foreign_key: { to_table: :users }

Update Existing:

If you are adding references to an existing table, you can do this:

add_reference :posts, :author, foreign_key: { to_table: :users }

^{Note: The default value for index is true.}

edited Mar 26 '17 at 22:35

answered Feb 05 '17 at 19:11

Sheharyar

73,588
21
168
215

Will the initial definition allow nulls? If not, do you know the nullable alternative? – Vorpulus Lyphane May 19 '17 at 09:46
10

This definition allows `null`s. To not allow them, add the usual option `null: false`. – Ashitaka Nov 27 '17 at 11:30
1

Thanks. For the "Initial Definition", I think the "index: true" isn't necessary. I get the same schema change with or without it. Never mind; just saw your note at the end. – Joey Dec 15 '17 at 00:49
Doesn't `references` add the index automatically? – fatfrog Oct 12 '22 at 16:05
1

Yes, but I like being explicit. – Sheharyar Oct 28 '22 at 18:47

ecoologic · Answer 2 · 2018-02-06T11:42:38.667

262

In Rails 4.2+ you can also set foreign keys in the db as well, which is a great idea.

For simple associations this can be done also on t.references adding foreign_key: true, but in this case you'll need two lines.

# The migration
add_reference :posts, :author, index: true
add_foreign_key :posts, :users, column: :author_id

# The model
belongs_to :author, class_name: "User"

edited Feb 06 '18 at 11:42

answered Apr 11 '15 at 12:27

ecoologic

10,202
3
62
66

2

Thanks, but the question is tagged Rails3, I'm happy to just help out – ecoologic Feb 17 '16 at 23:27
2

Ooh, I didn't notice that. Well, it's been very helpful to.me. :) – bonh Feb 18 '16 at 01:18
2

@ecoologic, just one thing you might want to add, add_foreign_key is only rails 4.2+. ;) – Dan Williams May 13 '16 at 14:57
Is there a syntax for the has_many that goes along with this? – Jeffrey Basurto Jun 05 '16 at 08:08
@JeffreyBasurto the column always goes on the `belong_to` part of the relation, I hope this answers your question – ecoologic Jun 05 '16 at 13:17
4

I'm not sure you need the `references: :users` option in the `add_reference` call. I don't see it documented in the docs and it seems to work on my end without it. – jakecraige Feb 28 '17 at 22:24
2

Looking at the source, it indeed doesn't do anything with an optional `references` argument. And I think the way it's set up it doesn't matter if there is not a table called `authors`, all it's doing is adding a column called `author_id`. It *does* matter if you want to create a foreign key constraint though, because it does try to use that as the table name then, so if you wanted to do that, you'd have to have a separate add_foreign_key statement. – Ibrahim Feb 05 '18 at 23:11
Hi guys, thank you for the correction, I'm updating this right now. I can't remember where I got that from (it's been a few years). Thank you. – ecoologic Feb 06 '18 at 11:42

nathanvda · Answer 3 · 2019-05-16T11:55:54.540

104

In rails 4, when using postgresql and the schema_plus gem you can just write

add_reference :posts, :author, references: :users

This will create a column author_id, which correctly refers to users(id).

And in your model, you write

belongs_to :author, class_name: "User"

Note, when creating a new table you can write it as follows:

create_table :things do |t| 
  t.belongs_to :author, references: :users 
end

Note: the schema_plus gem in it's entirety is not compatible with rails 5+, but this functionality is offered by the gem schema_auto_foreign_keys (part of schema_plus) which is compatible with rails 5.

edited May 16 '19 at 11:55

answered Mar 13 '14 at 16:04

nathanvda

49,707
13
117
139

35

and if you are using `create_table`: `t.references :author, references: :users` – Michael Radionov Apr 06 '14 at 16:28
2

Adding @MichaelRadionov's comment to your answer would make it perfect. – toxaq Apr 11 '15 at 23:35
2

I've been looking at the Rails 4.1 source, and I can't find any evidence that `:references` actually does anything. – jes5199 Nov 08 '15 at 05:57
1

Yes you are right, I have been using the `schema_plus` gem for ages, and it is actually adding that functionality. I edited my answer accordingly. – nathanvda Jan 27 '16 at 15:02
3

In Rails 6, it seems like the syntax `t.references :col_name, references: other_table_name` works without installing extra gems. – Qqwy Sep 09 '19 at 14:26

score 67 · Accepted Answer · edited Dec 04 '12 at 01:35

67

Do it manually:

add_column :post, :author_id, :integer

but now, when you create the belongs_to statement, you will have to modify it, so now you have to call

def post
    belongs_to :user, :foreign_key => 'author_id'
end

edited Dec 04 '12 at 01:35

Tom Harrison

13,533
3
49
77

answered Dec 04 '12 at 01:28

mschultz

1,295
13
21

1

Don't I have to add any index? – caarlos0 Dec 04 '12 at 01:33
1

Yes, you'll need to create an index in the migration. – Tom Harrison Dec 04 '12 at 01:36
1

Rails cheats - it doesn't really use indexes by default. Now if you want indexes (which are a great idea - despite the fact that rails will completely ignore them), than you can certainly add them. You will want to check out the guide I link for more info on migrations in general, and you may even end up putting calling SQL code directly in your migration. I would say ignore it, as it isn't a normal part of rails, you will get 0 performance out of it, as rails' default generated SQL queries take no advantage of it. [link](http://guides.rubyonrails.org/migrations.html) – mschultz Dec 04 '12 at 01:39
using `schema_plus` gem, `t.references :category, index: true, foreign_key: true, references: :match_categories` also worked for me in migration file. – elquimista Feb 27 '16 at 22:17
I would suggest to use `bigint` instead of `integer` as it's the [default column type for primary keys for Rails 5.1+](https://github.com/rails/rails/pull/26266) – Giovanni Benussi Dec 11 '18 at 17:27
It's probably worth noting, at this point, that ALL of this is dated, particularly since Rails5, which has better ways of doing this. – mschultz Oct 23 '19 at 15:29

score 53 · Answer 5 · edited Feb 05 '17 at 19:02

53

If you aren't using a foreign key, then it doesn't matter what the actual table name of the other table is.

add_reference :posts, :author

As of Rails 5, if you're using a foreign key, you can specify the name of the other table in the foreign key options. (see https://github.com/rails/rails/issues/21563 for discussion)

add_reference :posts, :author, foreign_key: {to_table: :users}

Prior to Rails 5, you should add the foreign key as a separate step:

add_foreign_key :posts, :users, column: :author_id

edited Feb 05 '17 at 19:02

Sheharyar

73,588
21
168
215

answered Nov 08 '15 at 06:14

jes5199

18,324
12
36
40

Note that `rails g migration AddAuthorToPosts author:references` can't output this. You have to manually update the migration (see: https://github.com/rails/rails/blob/main/railties/lib/rails/generators/rails/model/USAGE) – Christopher Oezbek Feb 26 '21 at 11:03

score -3 · Answer 6 · answered Mar 30 '17 at 16:11

-3

alias_attribute(new_name, old_name) is very handy. Just create your model and the relationship:

rails g model Post title user:references

then edit the model and add an attribute alias with

alias_attribute :author, :user

After that you'll be able to run things like

Post.new(title: 'My beautiful story', author: User.first)

answered Mar 30 '17 at 16:11

sekmo

1,517
18
27

1

this does not work when you need to define multiple references to another model, e.g., post (author, editor) – ultrajohn Apr 07 '17 at 02:56

Specifying column name in a "references" migration

6 Answers6

For Rails 5+

Initial Definition:

Update Existing:

Linked