At 09:23 AM 12/7/2007, [EMAIL PROTECTED] wrote:
Date: Fri, 07 Dec 2007 14:22:26 +0100
From: "Stefan Scheidegger" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: INSERT INTO relational tables
Message-ID: <[EMAIL PROTECTED]>
Hi all
I'm new to SQL and I'm facing a problem I can't
find any information about (google, forums). By
now I wonder if I understood something wrong about relational DBs.
An example to explain my Problem:
Lets say I have a table containing information
about the customer (name, address, ...) and
about his order (pieces, product-name, price).
Because one customer can order several products
I split the table into two relational tables to prevent redundancy:
tbl_customer (cust_id, cust_name, cust_address)
and
tbl_order (ord_pieces, ord_productname,
ord_price, ord_customer REFERENCES tbl_customer(cust_id))
Now I want to insert several new customers with
several orders each at once. If I had all
information in one table, this would be easy with something like:
INSERT INTO tbl_customerorders (name, address,
pieces, porductname, price) VALUES ('MR. X',
'1st street', 3, 't-shirts', 30), ('MR. X', '1st
street', 5, 'books', 50), ('MRS. Y', '2nd
street', 1, 't-shirt', 10),...
But how can I do this in one query if I split
the table? I can add one new customer, get his
ID with curval() and then add his orders. But
this wonât work if I want to add several customers at once.
To read this information I can do a query with
the argument WHERE cust_id=ord_customer. I can
create a VIEW doing this so I can read the data
as if it was stored in only one table. But is
there in posgres/sql an abstraction-layer that
allows me to insert as if the information was
stored in one table? (Something like a VIEW that
provides INSERT, UPDATE, ⦠and automatically
inserts the referenced ID.)
Thanks for any help!
Stefan
Andrea has given you some "deep" answers (i.e.
smarter than what I'm going to say) but since you
say you're a newbie to SQL maybe this will help.
Perhaps all you need is to wrap your entire set
of statements into a transaction
(http://www.postgresql.org/docs/8.2/interactive/tutorial-transactions.html)
to ensure that your entires are "acid"
(http://databases.about.com/od/specificproducts/a/acid.htm).
BEGIN
insert tbl_customer...;
insert tbl_order...;
insert tbl_order...;
END
It's not clear whether you're looking for syntax
simplicity or referential integrity (or both). If
syntax is your need, then Andreas has given some good ideas.
As another thought about syntax enhancement:
there are lots of object-relational mappers out
there that let you model your relations in a
higher level language (I use Ruby and
ActiveRecord which are easy to write and learn,
but Java, .NET, etc all have great stuff). Then
you just build your "entities" in the domain
specific language and it handles all the id
inserts and relational mapping for you. They can
even handle mapping many-to-many joined entities,
if you're careful in setting it up.
I hope this is helpful,
Steve
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
