09 May 2008, 16:03
Generic-user-small

Arthur Blair (2 posts)

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?)

09 May 2008, 16:21
Samr_small_pragsmall

Sam Ruby (584 posts)

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

Foreign keys are a “bit more involved”:http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html with sqlite3. I’m thinking of deferring this to late in chapter 16.

09 May 2008, 16:56
Generic-user-small

Arthur Blair (2 posts)

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.

12 May 2008, 18:09
Generic-user-small

Casey Smith (5 posts)

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) =========================================

== 6 CreateLineItems: migrating =============================================== – create_table(:line_items) rake aborted! SQLite3::SQLException: table line_items already exists: CREATE TABLE line_items (“id” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, “product_id” integer NOT NULL, “order_id” integer NOT NULL, “quantity” integer NOT NULL, “total_price” decimal (8,2) NOT NULL, “created_at” datetime DEFAULT NULL, “updated_at” datetime DEFAUL T NULL)

(See full trace by running task with –trace)

Thanks for the help.

12 May 2008, 20:47
Samr_small_pragsmall

Sam Ruby (584 posts)

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.

12 May 2008, 23:29
Generic-user-small

Casey Smith (5 posts)

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) =========================================

== 6 CreateLineItems: migrating =============================================== – create_table(:line_items) rake aborted! SQLite3::SQLException: table line_items already exists: CREATE TABLE line_items (“id” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, “product_id” integer NOT NULL, “order_id” integer NOT NULL, “quantity” integer NOT NULL, “total_price” decimal (8,2) NOT NULL, “created_at” datetime DEFAULT NULL, “updated_at” datetime DEFAUL T NULL)

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

13 May 2008, 00:07
Samr_small_pragsmall

Sam Ruby (584 posts)

Try:

sqlite3 db/development.sqlite3 "drop table line_items;"
rake db:migrate
13 May 2008, 00:44
Generic-user-small

Casey Smith (5 posts)

Awesome thanks Sam, I think it worked.

01 Feb 2010, 07:09
Generic-user-small

David Wilkie (2 posts)

There is now a plugin that supports limited foreign key support and enforcement in SQLite3. See http://github.com/dwilkie/foreigner

02 Feb 2010, 04:37
Generic-user-small

David Wilkie (2 posts)

or you can use http://github.com/dwilkie/sqlite-foreigner if you don’t need support for other databases

  You must be logged in to comment