# OT (Mostly): Problem with SQL

**URL:** <https://www.ruby-forum.com/t/ot-mostly-problem-with-sql/49488>\
**Category:** Rails\
**Created:** [December 5, 2005, 10:31am UTC](https://www.ruby-forum.com/t/ot-mostly-problem-with-sql/49488 "2005-12-05T10:31:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rbywater](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/898d66/32.png) [@rbywater](https://www.ruby-forum.com/u/rbywater)\
**Post date:** [December 5, 2005, 10:31am UTC](https://www.ruby-forum.com/t/ot-mostly-problem-with-sql/49488/1 "2005-12-05T10:31:42Z")

</div>

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 🙂 ). MySql version is 4.1.15.

Thanks for any assistance,  
Richard.

---

<div class="post-metadata">

**Author:** ![rbywater](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/898d66/32.png) [@rbywater](https://www.ruby-forum.com/u/rbywater)\
**Post date:** [December 5, 2005, 10:35am UTC](https://www.ruby-forum.com/t/ot-mostly-problem-with-sql/49488/2 "2005-12-05T10:35:46Z")

</div>

On 12/5/05, Richard B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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.

---

<div class="post-metadata">

**Author:** ![rbywater](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/898d66/32.png) [@rbywater](https://www.ruby-forum.com/u/rbywater)\
**Post date:** [December 5, 2005, 8:33pm UTC](https://www.ruby-forum.com/t/ot-mostly-problem-with-sql/49488/3 "2005-12-05T20:33:39Z")

</div>

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.