Insert into two tables

Insert into two tables

Post by Robby Slaughte » Wed, 20 Jun 2001 06:39:46



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

-----Original Message-----


Sent: Monday, June 18, 2001 2:05 PM
To: Postgres Novice
Subject: [NOVICE] Display version

I've just figured out how to display the PostgreSQL version number in
Cold Fusion. Here's the code:
<CFQUERY NAME="getversion" DATASOURCE="yourdatasource"> SELECT
version();</CFQUERY>
<CFOUTPUT>#getversion.version</CFOUTPUT>
Best regards,
Frank Hilliard
http://frankhilliard.com/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

Insert into two tables

Post by Robby Slaughte » Wed, 20 Jun 2001 08:14:46



>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:
----------------------------------

Quote:> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

1. Insert into two tables

On Mon, Jun 18, 2001 at 04:18:57PM -0500, some SMTP stream spewed forth:

*bonk* What about the transaction that completed before you did the
select...limit?

Er, I usually just do this:

select nextval('sequence');
insert into table(id_col) values (<nextval from above>);

That way you are guaranteed a unique sequence value.

You can also do this as:
insert into table (all_columns_except_serial) values (whatever);

gh

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

2. sparc5 question

3. Insert in 2 tables - Second insert fails then first, then second, then first...

4. What are the keysyms for Hyper and Super?

5. Comparing two tables, update some then insert the rest

6. GTA:VC accompanied soundtrack collection

7. unable to insert data into two tables at same time

8. ?? NeXTSTEP on IBM THINKPAD 755 ??

9. Returning PK of first insert for second insert use.

10. Insert values from one existing table into a new table.

11. Preference between the two methods of inserting negatives into the Nikon Coolscan IV / 4000 ?

12. Q: Want to insert a second X-axis