I have an old database that I need to import into apache derby. This seemed to be a pretty easy thing to do, except when I realized that my autogenerated ID column is stuck thinking the next autoincrement value is 1. i.e. since my import statements from the old database included IDs (which are important for foreign key associations), I do not allow (and cannot allow) derby to autogenerate them. So, I need to find some way to tell derby to update the next autogenerate value to the one more than the maximum of the ID in each of my tables.
So, I looked up tableid from sys.systables, and matched it with referenceid from sys.syscolumns, and was going to change autoincrementvalue to be this number, then got the following error: java.sql.SQLSyntaxErrorException: 'SYS.SYSCOLUMNS' is a system table. Users are not allowed to modify the contents of this table.
Boo. Is there another way to do this? This seems like a pretty common thing to do, but can't seem to find the standard way to do it...