Re: INSERT INTO relational tables
От | Steve Midgley |
---|---|
Тема | Re: INSERT INTO relational tables |
Дата | |
Msg-id | 20071209075032.2A1112E3294@postgresql.org обсуждение исходный текст |
Ответ на | INSERT INTO relational tables ("Stefan Scheidegger" <Stefan.Scheidegger@gmx.net>) |
Ответы |
Re: INSERT INTO relational tables
|
Список | pgsql-sql |
At 09:23 AM 12/7/2007, pgsql-sql-owner@postgresql.org wrote: >Date: Fri, 07 Dec 2007 14:22:26 +0100 >From: "Stefan Scheidegger" <Stefan.Scheidegger@gmx.net> >To: pgsql-sql@postgresql.org >Subject: INSERT INTO relational tables >Message-ID: <20071207132226.281710@gmx.net> > >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
В списке pgsql-sql по дате отправления: