OT (Mostly): Problem with SQL

Rails
1

Hi,

This is mostly OT for this list but as I’m trying to go through the
Agile
Web Dev with Rails book I thought someone else might have come across
this…

Basically I’m trying to start Iteration D1 (capturing an order) but I
can’t
seem to get the SQL to run in my MySql database.

The SQL I’m trying to run is:
drop table if exists line_items;
drop table if exists orders;
drop table if exists products;

create table products (
id int not null auto_increment,
title varchar(100) not null,
description text not null,
image_url varchar(200) not null,
price decimal(10,2) not null,
date_available datetime not null,
primary key (id)
);

create table orders (
id int not null auto_increment,
name varchar(100) not null,
email varchar(255) not null,
address text not null,
pay_type char(10) not null,
primary key (id)
);

create table line_items (
id int not null auto_increment,
product_id int not null,
quantity int not null default 0,
unit_price decimal(10,2) not null,
constraint fk_items_order foreign key (order_id)
references
orders(id),
constraint fk_items_product foreign key (product_id)
references
products(id),
primary key (id)
);

However when I run this I get: ERROR 1072 (42000) at line 24: Key column
‘order_id’ doesn’t exist in table

I can’t see what the problem is and its stopping me from moving on to
the
more interesting stuff. Does anyone have any ideas? (I’m sure it will be
something realllllly simple :slight_smile: ). MySql version is 4.1.15.

Thanks for any assistance,
Richard.

2

On 12/5/05, Richard B. [email protected] wrote:

);

However when I run this I get: ERROR 1072 (42000) at line 24: Key column
‘order_id’ doesn’t exist in table

The problem is that you are missing the order_id field in line_items
table and thus when you try to add a constraint using that column it
is complaining. Try

create table line_items (
id int not null auto_increment,
product_id int not null,
order_id int not null,
quantity int not null default 0,
unit_price decimal(10,2) not null,
constraint fk_items_order foreign key (order_id)
references orders(id),
constraint fk_items_product foreign key (product_id)
references products(id),
primary key (id)
);

–
Cheers,

Peter D.

3

lol - you wouldn’t guess how many times I must have gone through the
columns
and not picked that up…

Thanks Peter - I’ll give it a go.

Richard.