On Sun, Aug 14, 2005 at 11:58:17PM -0700, James Washer wrote: > My first question: If I want to insert a row in a table ONLY if that row does > not already exist in the table, how is this normally done? I know I can > define the table requiring entries to be unique, but I assume that means I'll > generate an error condition if I try to insert a duplicate. Is that the "best > way"?
this depends highly on what the data is and how it's collected. most of the time, new data will simply be unique, or it's made instantly unique by a auto_increment field. If you can't be sure ahead of time whether your insertion will work, you either have to run a select command to look in advance for the record, or you have to insert and check your error code. inserting and checking for an error would be most efficient. > A few more details. I have a many to one relationship. For example, let's say > I take an order from a customer. If the customer is new, I need to enter them > into the database. Further, I need to "link" the order to the customer. How > is this normally done. BTW, I'm using perl/DBD/DBI to handle the data entry. If you want to make sure all or nothing happens then use transactions. In this situation, I'd say adding a new customer could be considerred an operation that may be performed with or without a new order being successfully linked, so there's no reason to bind the two insertions in this way. just create a new customer, then somehow get the new customer id and use that as part of the order data you insert later. If a customer cannot exist without a linked order then do a "BEGIN" transaction before you insert anything, then "COMMIT or "ROLLBACK" depending on whether errors were encountered. > Oh, btw, this needs to be fairly efficient, since I'm entering 20K records > per hour. (no, this isn't really an customer order database, it's a telemetry > logging application, but it was easier to explain as a customer order system) I doubt 20K/hour (5.5/second) will be a problem. - Ben _______________________________________________ RLUG mailing list [email protected] http://lists.rlug.org/mailman/listinfo/rlug
