If you set the Identity column to generate by default, you should be able to insert the rows that already have a value, as long as that value is not already in the table.


The catch is that your sequence will be screwed up.


I think that there’s a way to do a work around using a stored procedure and an after trigger statement, however, yes you point out why this bug is not a small thing.


With respect to the specs, well, derby is wrong, no matter what those from IBM or Sun would like to believe. ;-)


Its kind of sad, really. The fix is really trivial.



From: []
Sent: Thursday, June 15, 2006 1:05 PM
To: Derby Discussion
Subject: RE: "generated by default" question


Here is a different view to the problem.

I have a bulk export-import routine that exports all the tables into .del files for backup using SYSCS_UTIL.SYSCS_EXPORT_TABLE and SYSCS_UTIL.SYSCS_IMPORT_TABLE procedures.

The export-import worked just fine until I realized that I cannot INSERT into any of the tables that have IDENTITY columns. This is because while importing all the identity columns are filled "manually".
So, the autoincrement counter is still at 0 while there already are rows with higher values.

I worked around that issue by using 'ALTER TABLE columnName ALTER COLUMN columnName RESTART WITH (current_max + 1)'.

But this proves that the current derby behavior is misleading if not wrong (no matter what the specs suggest). Derby SHOULD generate a unique id value.

I am pretty sure that, for someone who is familiar with the code fixing the bug will be quicker and less painful than continuing with this debate.