Auto-increment datatypes and JDBC
От | Simon Brooke |
---|---|
Тема | Auto-increment datatypes and JDBC |
Дата | |
Msg-id | m21z01l2w3.fsf@gododdin.internal.jasmine.org.uk обсуждение исходный текст |
Список | 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 ]
В списке pgsql-general по дате отправления:
Предыдущее
От: "Romanenko Mikhail"Дата:
Сообщение: Trouble with float4 after upgrading from 6.5.3 to 7.0.2