CODEWITHSUNDEEP
ruby-on-rails
wyc
wyc

Reputation: 55263

SQLite3::SQLException while trying to set foreign keys?

I have three models: User, Micropost, and Comment. I'm trying to set foreign keys as follows:

class CreateComments < ActiveRecord::Migration
  def change
    create_table :comments do |t|
      t.text :content

      t.timestamps
    end

    add_index :comments, :micropost_id, :user_id
  end
end

But I get this error:

An error has occurred, this and all later migrations canceled:

SQLite3::SQLException: near "user_id": syntax error: CREATE user_id INDEX "index_comments_on_micropost_id" ON "comments" ("micropost_id")

I understand that Rails insert foreign keys based on belongs_to and has_many declarations in the models. But I have everything set:

comment.rb:

class Comment < ActiveRecord::Base
  belongs_to :micropost
  belongs_to :user
end

micropost.rb:

class Micropost < ActiveRecord::Base
  attr_accessible :title, :content

  belongs_to :user
  has_many :comments
end

user.rb:

class User < ActiveRecord::Base
  # Include default devise modules. Others available are:
  # :token_authenticatable, :encryptable, :confirmable, :lockable, :timeoutable and :omniauthable
  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :trackable, :validatable

  # Setup accessible (or protected) attributes for your model
  attr_accessible :email, :password, :password_confirmation, :remember_me

  has_many :microposts
  has_many :comments
end

Any suggestions to fix this?

Upvotes: 0

Views: 177

Answers (1)

Baldrick
Baldrick

Reputation: 24340

If you want to create an index on 2 columns, the syntax is add_index table_name, [column1_name, column2_name], options. You also need to define the columns in the table (ActiveRecord does not add them automatically when you add belongs_to in the model class). So your migration should be

class CreateComments < ActiveRecord::Migration
  def change
    create_table :comments do |t|
      t.text :content
      t.integer :micropost_id
      t.integer :user_id

      t.timestamps
    end

    add_index :comments, [:micropost_id, :user_id]
  end
end

Upvotes: 1

Related Questions