What is the best way to provide a unique identifier as a primary key that will work in a database independent manner? I'm looking for functionality similar to Oracle's proprietary MY_SEQ.NEXTVAL.

Joe Sam Shirah

The "best way" probably lies in the eye of the beholder and/or the requirements of the particular application. Here are some possibilities in no particular order:

1) For databases that support them, an insert trigger or stored procedure that uses a proprietary means such as MY_SEQ.NEXTVAL OR that uses one of the following methods.

2) Use a server socket whose job is to control and hand out new identifiers, either with proprietary means or one of the following.

3) Use a singleton object whose job is to control and hand out identifiers. The identifiers would typically be tracked in a table containing a row for each table in the database which uses this mechanism. Each row would have the affected table name as the key and a numeric column containing the next key. The singleton can get identifiers one at a time or in groups ( at the potential price of skipping some identifiers. )

4) Do the same as 3 in your own individual classes.

For 2), 3) and 4) either the supporting mechanism or your individual classes must be prepared to handle identifier clashes, since there is no way to guarantee that all database operations will use this mechanism in a production environment.

Other suggestions have included random numbers with checks for clashes and trying to generate a totally unique number internally. The problem with the last is complexity and the size of the generated key, which can impact performance.