Forum: Ruby on Rails OT (Mostly): Problem with SQL

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
3be032a9b403edfd49b7b0ec329fb328?d=identicon&s=25 rbywater (Guest)
on 2005-12-05 10:31
(Received via mailing list)
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.
67b6389be42524fbd776e44fd35c3d7e?d=identicon&s=25 peter.j.donald (Guest)
on 2005-12-05 10:35
(Received via mailing list)
On 12/5/05, Richard Bywater <rbywater@gmail.com> 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 Donald
3be032a9b403edfd49b7b0ec329fb328?d=identicon&s=25 rbywater (Guest)
on 2005-12-05 20:33
(Received via mailing list)
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.
This topic is locked and can not be replied to.