Entity EJBs - what to use as the primary key ?

[bdturne]

I have a quandry about what to use for the primary key class of my Entity EJB.

The current setup I have is we have an Oracle database with certain stored procedures for creating / deleting / modifying the "users" table.
In the users table is an "auto-generated" key column (called userPK) and an id column (called userID).

Whilst userPK is the "primary key" in Oracle, it is also true that userID is always unique (and a lot more meaningful that the userPK integer number).

So, I believe for the primary key for my entity bean, I should continue to user the userPK column - as it is still the primary key in the database. But this key is auto-generated in Oracle, thus when my Java procedure creates the user, it doesn't automatically know the key
I guess I can add a select statement to get it afterwards though, but it would be a SELECT userPK from users where userID = '######' - the problem with this query being it is not 100% garunteed to return the latest entry (as someone could have setup another user with the same ID by accident ?)

So maybe I should use the userID as the primary key ? Again, I get the problem with select statements, but this time when I go to delete the user. E.g. I will have to select the userPK from the database so I can runthe stored proc to delete a User, but what if there are multiple users with the same ID ?

Overall, there shouldn't ever be 2 user's with the same ID, but assumption being the mother of all... mistakes, I don't want to ever assume this.

SO what are people thoughts on using "auto-key" columns as the primary key ? Or should these be left to just the database for it's own integraty, and a different key used for the Entity Beans ?

Cheers,
Ben

[roytruelove]

Hi Ben,

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,
Roy

[hstech]

Hi Ben,

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,

Aaron.

[bdturne]

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 ?)

Ben

[jmschust]

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.

Any hints?