Auto-increment datatypes and JDBC - Mailing list pgsql-general

The following message is a courtesy copy of an article
that has been posted to comp.lang.java.databases as well.

I have a Java database toolkit which I'm trying to make as portable as
possible. It has to work with databases with minimal features as well
as with databases with more features.

The particular issue I'm struggling with at present is auto-increment
data types, which are convenient for abstract primary keys. What
MS-Access calls an 'Auto Number' and Postgres calls a
'serial'. Because not all databases have these, my toolkit has to be
able to generate new unique key values, which it does for numeric
fields by finding the maximum value currently in the column and
incrementing it by one.

This actually works for Postgres 'serial' fields in as much as a new
unique key is generated and the database accepts it. However, if a new
row is subsequently inserted into the table with no key value the
insert fails with 'ERROR: Cannot insert a duplicate key into unique
index address_pkey'. The problem here is that Postgres is storing what
it thinks the next key value should be, but I've already used that
one. Under the same circumstances, MS-Access copes fine, presumably
because it too is using a 'max + 1' algorithm for incrementing the
Auto Number.

Now the issue here as far as I'm concerned is not really which is
right and which is wrong, but how do I cope gracefully with both
behaviours. Ideally the database should tell me that it has an
auto-increment field, but the only potentially relevent type in
java.sql.Types is 'DISTINCT', and the documentation doesn't make it at
all clear whether 'DISTINCT' is intended to represent an
auto-increment data type or not. In any case, postgresql.Driver
returns 'INTEGER' if you ask for ResultSetMetaData.getColumnType() on a
'serial' column, and MS-Access returns 'INTEGER' when you ask for it on
an 'Auto Number'.

So whatever the theory, I'm not in practice able to distinguish
auto-increment fields by metadata.

My options are

(i) Assume everything auto-increments unless proven otherwise, and
send inserts with no value in the primary key field. The problem here
is the granularity of SQLExceptions is very coarse; I can't trap for a
NullKeyValueException because java.sql doesn't define any such
thing. Some databases, where the primary key has not been defined as
'not null' (or the database engine doesn't understand 'not null') will
allow me to build up large numbers of records with duplicate (null)
primary keys. So that solution doesn't work.

(ii) Assume everything does not auto-increment, and always generate my
own key values. But this means that in Postgres (and possibly in other
RDBMS) other applications using the same database and depending on the
auto-increment feature will fail. So that solution doesn't work

(iii) Make key generation configurable, so that the local
administrator can tell my applications which tables to generate keys
for and which not to. This solution works, but I don't much like it,
because it places too much reliance on the administrator knowing, and
my applications will break if (s)he gets things wrong.

--
simon@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

        [ This .sig subject to change without notice ]