SQLite3::SQLException: near "CONSTRAINT": syntax error

Question

Can you help me with this problem. First I create a migration that generates three tables. Then I try execute a sql query but for some reason an error is triggered I am a newbie. I am reading Rails Guides in it version four (4.0.6 Ruby On Rails)

class ExampleMigration < ActiveRecord::Migration
      def up
        create_table :categories do |t|

        end


        create_table :users do |t|
          t.string :email
        end


        create_table :products do |t|
          t.references :category
        end

        execute <<-SQL
          ALTER TABLE products
            ADD CONSTRAINT fk_products_categories
            FOREIGN KEY (category_id)
            REFERENCES categories(id)
        SQL




        add_column :users, :home_page_url, :string
        rename_column :users, :email, :email_address
      end

      def down
        # add a foreign key
        execute <<-SQL
          ALTER TABLE products
            DROP FOREIGN KEY fk_products_categories
        SQL

        rename_column :users, :email_address, :email
        remove_column :users, :home_page_url

        drop_table :products
        drop_table :users
        drop_table :products
      end

Ubuntu Console

trabajo@fernando:~/estudio/MigrationsGuides$ rake db:drop
trabajo@fernando:~/estudio/MigrationsGuides$ rake db:migrate
==  ExampleMigration: migrating ===============================================
-- create_table(:categories)
   -> 0.0019s
-- create_table(:users)
   -> 0.0012s
-- create_table(:products)
   -> 0.0007s
-- execute("      ALTER TABLE products\n        ADD CONSTRAINT fk_products_categories\n        FOREIGN KEY (category_id)\n        REFERENCES categories(id)\n")
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

SQLite3::SQLException: near "CONSTRAINT": syntax error:       ALTER TABLE products
        ADD CONSTRAINT fk_products_categories
        FOREIGN KEY (category_id)
        REFERENCES categories(id)
/home/trabajo/.rvm/gems/ruby-2.0.0-p481@MigracionesGuides/gems/sqlite3-1.3.9/lib/sqlite3/database.rb:91:in `initialize'
/home/trabajo/.rvm/gems/ruby-2.0.0-p481@MigracionesGuides/gems/sqlite3-1.3.9/lib/sqlite3/database.rb:91:in `new'
/home/trabajo/.rvm/gems/ruby-2.0.0-p481@MigracionesGuides/gems/sqlite3-1.3.9/lib/sqlite3/database.rb:91:in `prepare'
/home/trabajo/.rvm/gems/ruby-2.0.0-p481@MigracionesGuides/gems/sqlite3-1.3.9/lib/sqlite3/database.rb:134:in `execute'

Answer 1

From the fine SQLite manual:

Enabling Foreign Key Support

In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined. [...] If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error).

Seems that your SQLite was compiled with OMIT_FOREIGN_KEY defined. Either recompile SQLite with FKs and triggers enabled or (better IMO) install a proper database that fully supports FKs out of the box and switch to that database; I'd recommend PostgreSQL but MySQL should do if you already have it.

In any case, always develop on top of the same database you're going to be deploying on; this applies even to things you're hacking up to play around. Whatever database you use will give you habits and those habits won't carry over to other databases. ORMs offering database portability is a myth, don't fall for it.