Migration: 'unique' column constraint


#1

I’m using a legacy sqlite3 database for an app I’m developing. I’m
trying to create a migration that creates a table containing a column
with a ‘unique’ constraint. Here’s my migration:

class CreateEvents < ActiveRecord::Migration
def self.up
create_table :events do |t|
#execute “name text NOT NULL UNIQUE”
t.column :name, :text, :null => false, :unique => true
t.column :cmd, :text, :null => false
t.column :valid, :integer, :null => false
t.column :arg1, :text
t.column :arg2, :text
t.column :arg3, :text
t.column :arg4, :text
t.column :arg5, :text
end
end

def self.down
drop_table :events
end
end

The :unique constraint was quietly ignored by the migration, so the name
column ended up looking like this: “name text NOT NULL”. I also tried
executing the equivalent sql in place of the “t.column :name” statement,
but received an invalid sql error: “SQLite3::SQLException: near “name”:
syntax error: name text NOT NULL UNIQUE”.

I can’t create the column after the table creation becaues sqlite3
doesn’t support the ‘unique’ constraint in an “ALTER TABLE” statement.

Any ideas on how I can make the ‘name’ column unique? This must be done
in the schema, not in the model, because other apps already use the
current schema.

Thanks,
Earle