I take the easy approach. I use Oracle Designer and when I transform an entity to get a table, I check the checkbox that indicates "surrogate key". It automatically gives me a sequence generated surrogate primary key. The name of the sequence by default is the table alias followed by "_SEQ" so it all matches up nicely. When I'm ready, I generate the DDL and it all gets generated neatly and correctly. BTW, when I transform the entity, if I've got a primary unique identifier on the entity then I get a unique key automatically along with my sequence generated surrogate primary key. It's a great way to do it and very easy!
Respectfully, > Maggie Tompkins - CAD SQA > Corporate Applications Division > Technology Services Organization - Kansas City > Defense Finance and Accounting Service > 816-926-1117 (DSN 465); [EMAIL PROTECTED] > -----Original Message----- Sent: Wednesday, November 05, 2003 7:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the "roll your own sequence method". The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: TOMPKINS, MARGARET INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
