RE: "generated by default" question

[derby]

Ateesh,   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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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. Thanks -Ateesh