Skip site navigation (1) Skip section navigation (2)

RE: Insert into two tables

From: "Robby Slaughter" <webmaster(at)robbyslaughter(dot)com>
To: "Frank Hilliard" <frankhilliard(at)home(dot)com>, "Postgres Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: RE: Insert into two tables
Date: 2001-06-18 22:54:31
Message-ID: EPEHLKLEHAHLONFOKNHNCEHFDBAA.webmaster@robbyslaughter.com (view raw or flat)
Thread:
Lists: pgsql-novice
Frank wrote:

>No, that's it. But put the whole thing inside <CFTRANSACTION> tags.

I was hoping I was wrong! Seriously, the docs seem to talk about
sequences and it seems like I should do something like this to
create my table

CREATE SEQUENCE orders_ordersid_seq;
CREATE TABLE orders
    (ordersid INTEGER DEFAULT nextval('orders_ordersid_seq');
   ...

...and then I should insert with something like:

1)  INSERT INTO TABLE orders VALUES ( ... )    /* unique id auto-generated!
*/
2)  INSERT INTO TABLE orderitems VALUES (nextval('orders_ordersid_seq'),
... )

but I don't know enough about referential integrity or how postgres really
works to know if that's right. If I do step 1 and then step 2, is the
sequence incremented between the two automatically so I need to do
nextval('..') - 1 ? Or is not incremented if I transactionalize the whole
thing?

Maybe I should do step 2 and then step 1, but that would seem to violate
referential integrity (the foreign key on orderitems would reference
a number that had not been created yet...)

help!!!  :-)

Thanks,
Robby


Original message:
----------------------------------

> This seems like such a common place procedure that I figure there had
> to be a "right" way to do it.
>
> I've got two tables, orders and order_items. orders has a primary
> key "ordersid" and order_items has a foreign key, orderid (which
> obviously references orders.ordersid)
>
> Say I want to create a new order and put some items into it. If
> I use an autoincrement field I could just:
>
>   INSERT INTO orders VALUES ( ... );
>
> And then I need to get the orderid I just created to create
> new records in the orderitems table So am I supposed to
> immediately do a:
>
>   SELECT ordersid FROM orders ORDER BY ordersid DESC LIMIT 1;
>
> And then get the value, and then do inserts in the order items
> table? Surely there's some way to wrap this all up into a
> nice little procedure or something.
>
> Thanks----
>
> -Robby


pgsql-novice by date

Next:From: GHDate: 2001-06-19 03:14:52
Subject: Re: Insert into two tables
Previous:From: Robby SlaughterDate: 2001-06-18 21:18:57
Subject: Insert into two tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group