migrations in rails (sqlite)

Question

hey I have a small problem with my rake

class CreateEvents < ActiveRecord::Migration

  def self.up
    create_table :events do |t|
      t.integer :broadcast_id
      t.integer :position
      t.string :title
      t.string :location
      t.string :link
      t.text :description
      t.datetime :time
  end
    add_foreign_key :events, :broadcast_id, :broadcasts
  end

  def self.down
    remove_foreign_key :events, :broadcast_id, :broadcasts
    drop_table :events
  end

end

problem => add_foreign_key :events, :broadcast_id, :broadcasts

$ rake db:migrate

== CreateEvents: migrating ===================================================
-- create_table(:events)
-> 0.0021s
-- add_index(:events, :broadcast_id)
-> 0.0004s
rake aborted!
An error has occurred, this and all later migrations canceled:

SQLite3::SQLException: near "FOREIGN": syntax error: ALTER TABLE "events" ADD FOREIGN KEY ("broadcast_id") REFERENCES "broadcasts"(id)

Dty · Accepted Answer · 2011-02-23T13:29:57.970

3

Why are you defining foreign keys this way?

If you want a relationship between your Events and Broadcasts then you should look into creating an active record relation. Something like

# Event model
class Event < ActiveRecord::Base
  belongs_to :broadcast
end

# Broadcast model
class Broadcast < ActiveRecord::Base
  has_many :events
end

This way you let rails maintain the foreign key relationship for you. Check out the Rails Guide on active record associations for more info.

edited Feb 23 '11 at 13:29

answered Feb 23 '11 at 13:22

Dty

12,253
6
43
61

Additionally, foreign key support in sqlite is [a bit more involved](http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html). – Jamie Forrest Feb 23 '11 at 15:51
thanks for the quick reply! Now I have saw that not in my models, it is a total bullshit code for rails!! – imalik8088 Feb 24 '11 at 13:12
@Dty, What do you mean by "let rails maintain the foreign key relationship for you." ? I stil lneed to invoke rails generate migration AddBroadcastToEvent references:broadcast . am I right ? – Yonatan Maman Jul 28 '11 at 21:08
@Yonatan, no. When you use something like `belongs_to` with `has_many` in rails you don't have to manually create the foreign key references. – Dty Jul 29 '11 at 05:59
1

http://guides.rubyonrails.org/migrations.html#special-helpers: "The references helper does not actually create foreign key constraints for you. You will need to use execute for that or a plugin that adds foreign key support." – Art Shayderov Aug 22 '11 at 08:28
My concern with this approach is with race conditions, should the foreign key support not be in the database to ensure data integrity? – Vinnyq12 Aug 07 '12 at 14:55

score 1 · Answer 2 · edited May 23 '17 at 10:24

As of version 3.6.19, SQLite, aka the default development database, supports foreign key constraints, but enforcement of foreign key constraints is turned off by default (for backwards compatibility), to fix it:
Suggested option:

Use another database such as mysql/postgresql as your development database in Rails, and below is an example:

default: &default
adapter: mysql2
encoding: utf8
pool: 5
username: user
password: password
socket: /tmp/mysql.sock

development:
<<: *shared
database: db_development

Other options include:

Future versions(4.*) of SQLite has enabled foreign key constraints in default, but as of now, it's not released yet.
We could also enable foreign key support by the following command:

sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys // should print 1

But please note:
- it does not persist and you have to do it on every connection;
- If PRAGMA foreign_keys print nothing, it means your version of SQLite does not support foreign key, please upgrade to 3.6.19 or higher version;
Some says we could enable it in connection string, but I didn't find out the correct string in Rails. You could refer to this answer.

migrations in rails (sqlite)

2 Answers2