Chapter 10.1 - Foreign key constraints with SQLite?

Page 141 uses MySQL statements to specify the foreign key constraints, which associate the rows of the line_items table with orders and products. However, using SQLite (as recommended earlier in the book) these statements give a syntax error:

...
-- create_table(:line_items)
   -> 0.0090s
-- execute("alter table line_items add constraint fk_line_item_products
             foreign key (product_id) references products(id)")
rake aborted!
SQLite3::SQLException: near "constraint": syntax error: alter table line_items add constraint fk_line_item_products
                 foreign key (product_id) references products(id)

Is there an alternative syntax that will work with SQLite? (Or have I done something wrong?)

My apologies, I’ll make sure that that will be fixed in the next beta.

Foreign keys are a bit more involved with sqlite3. I’m thinking of deferring this to late in chapter 16.

Indeed, the process of looking up foreign keys in sqlite myself scared me sufficiently to give up and ask here instead. I’ll see if I can get it to work myself, and look forward to the next beta.

Thanks for your rapid response.

I have a problem when I run “rake db:migrate” after I have created and editied the models “order” and “line_item” on page 140 of Check out. I get:

C:\rails\work\depot>rake db:migrate
(in C:/rails/work/depot)
5 CreateOrders: migrating ================================================
—create_table(:orders) -> 0.0780s
5 CreateOrders: migrated (0.0780s) =======================================

(See full trace by running task with—trace)

Thanks for the help.

For the moment, remove the two execute statements from db/migrate/006_create_line_items.rb. I have worked out a solution for SQLite3 that I will include in the next beta.

Thanks Sam but I am still getting the same error message. I have given the error message again and the text from the file. I am loving Ruby and Rails. Thanks for writing the books and help us noobs out.

C:\rails\work\depot>rake db:migrate
(in C:/rails/work/depot)
5 CreateOrders: migrating ================================================
—create_table(:orders) -> 0.0940s
5 CreateOrders: migrated (0.0940s) =======================================

File:

class CreateLineItems < ActiveRecord::Migration def self.up create_table :line_items do |t| t.integer :product_id, :null => false t.integer :order_id, :null => false t.integer :quantity, :null => false t.decimal :total_price, :null => false, :precision => 8, :scale => 2

t.timestamps
end

end
  def self.down
    drop_table :line_items
  end
end

Try:

sqlite3 db/development.sqlite3 "drop table line_items;" 
rake db:migrate

Awesome thanks Sam, I think it worked.