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
-- ad开发者_JAVA技巧d_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)
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.
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.sockdevelopment:
<<: *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.
精彩评论