CODEWITHSUNDEEP
ruby-on-railsmigrationsqlite
imalik8088
imalik8088

Reputation: 1641

migrations in rails (sqlite)

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)

Upvotes: 1

Views: 2953

Answers (2)

aqingsao
aqingsao

Reputation: 2104

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:

  1. Future versions(4.*) of SQLite has enabled foreign key constraints in default, but as of now, it's not released yet.

  2. 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;
  3. 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.

Upvotes: 1

Dty
Dty

Reputation: 12273

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.

Upvotes: 3

Related Questions