db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: "generated by default" question
Date Thu, 15 Jun 2006 18:58:02 GMT
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: Ateesh_Verma@rapid7.com [mailto:Ateesh_Verma@rapid7.com] 
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


Mime
View raw message