If you're not 100% sure that your userID column will always be unique, then you should definitely not use it. This leaves you with using userPK as the key. I had a similar problem with Oracle, but I made a little workaround. Oracle doesn't really have autogenerated keys in the same way that something like MS Access might. It uses 2 things, a trigger and a sequence. The sequence basically just counts in order, and the trigger inserts the value from the sequence into the table whenever another row is added.
What I did then was to query the sequence myself, and disable the trigger. For instance :
SELECT pulver.Companies_company_id_SEQ.NEXTVAL FROM DUAL
That will give you the unique ID, as well as automatically increment the sequence. You then can use this value as your primary key. Make sure you insert it in your row, however, because with the trigger disabled, this will no longer happen automatically.
Take it easy,
The problem with using UserID as the primary key for the database is that you wouldn't have to try too hard to think of scenarios where the user may want to change their UserID. If this is the primary key for the table, then the UserID key would need to be updated in all other tables for records that reference that user.
The solution is to either have the database generate the unique primary key, or have an algorithm that statistically guarantees the uniqueness of a key. A simple example of the latter is the GUID that M$ uses for ActiveX registration (oops, showing my heritage...).
If all database inserts are done from one machine, you could theoretically use the date/time as a unique key, but I wouldn't recommend this as a practical solution.
Hope this helps,
Some good points. There probably is a 1 in a million chnace that some user will need to change his user ID.
I wouldn't be using the ID as a primary key in the database though, so the tables would still be linked using the numeric key.
I guess the best approach is to turn off the triggers, and read the sequence numbers in manually (which is all the Oracle stored procedures I am replacing used to do anyhow).
The only probelm I can see with this approach is what if a client has got a user ID and he wants to update that user's details ? He cannot pass a key across, as he doesn't know the user's primary key at the moment, only his ID.
So I'm guessing he's going to do a "findByUserID" to get back a collection of keys for that userID (which 999,999 times in a million should return a collection of size one), then make an update call ?
Or is there a better way - as I don't really want clients to know about the numeric primary keys (they should be insulated from this - surely ?)
I'm having a little problem making that work though. Oracle is telling me that there is no return value from a "select seq.nextval from dual" or that it's a 'closed statement' when I tried to bind by field name 'NEXTVAL' or 'invalid column index' when trying to bind by the first column.