Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: How do you genrate primary keys?

Re[2]: How do you genrate primary keys?

From: Jonathan Gennick <>
Date: Thu, 06 Nov 2003 05:54:26 -0800
Message-ID: <>

Wednesday, November 5, 2003, 8:39:24 AM, Mercadante, Thomas F ( wrote: MTF> First, to me, a primary key should not be something that a user would ever MTF> see or use.

I'm not sure that's always practical. I once worked on a system that arbitrarily assigned ID numbers to vending stand managers. For example:

1001 John A. Smith
1002 Dave Smith
1003 John David
1004 John Q. Smith

I suppose we could have hidden these ID numbers from the users, but I'm not sure that would have been practical. ID numbers represent a fairly unambiguous way to reference people. Asking about "John Smith" is ambiguous, but asking about manager 1004 is not.

I suppose we could have generated two numbers, one hidden and one for the user to see, as in:

1, 1001, John A.Smith
2, 1002, Dave Smith,

And then the 1,2,... would be the primary key, hidden from the user, and 1001, 1002 would be the IDs the users saw. But this solution seems overly complex.

For the system I speak of, we generated the IDs from a sequence, assigned them when manager records were created, and we never had anyone decide to give a manager a different ID number. The key to success, in this, is likely that it was *our* system that *generated* the keys. With something like social security number, your not generating the key, but rather you are dependent on someone else for the value, and thus you should *not* use the social security number as a key. Instead, you should generate your own key, and leave social security number as an attribute.

Best regards,

Jonathan Gennick --- Brighten the corner where you are * 906.387.1698 *

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit, or send email to and include the word "subscribe" in either the subject or body.


Please see the official ORACLE-L FAQ:

Author: Jonathan Gennick

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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). Received on Thu Nov 06 2003 - 07:54:26 CST

Original text of this message