Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

First it's not understood why you are having a problem with creating a table with an auto-value. Over the last few days I've created dozens for date testing (as you may realize) and have no problems:

image description

However, I do see a problem when copying a table from an embedded DB to a split DB - the AutoValue changes to "No". You can fix this with two SQL statements. First change the field back to AutoValue:

ALTER TABLE YOURTABLENAME ALTER COLUMN YOURCOLUMNNAME SET GENERATED BY DEFAULT AS IDENTITY

Next get the highest value already in use in the table and add 1 to that for use in the next statement:

ALTER TABLE TOURTABLENAME ALTER COLUMN YOURCOLUMNNAME RESTART WITH XXX

where XXX = that highest value + 1.

First it's not understood why you are having a problem with creating a table with an auto-value. Over the last few days I've created dozens for date testing (as you may realize) and have no problems:

image description

However, I do see a problem when copying a table from an embedded DB to a split DB - the AutoValue changes to "No". You can fix this with two SQL statements. First change the field back to AutoValue:

ALTER TABLE YOURTABLENAME ALTER COLUMN YOURCOLUMNNAME SET GENERATED BY DEFAULT AS IDENTITY

Next get the highest value already in use in the table and add 1 to that for use in the next statement:

ALTER TABLE TOURTABLENAME ALTER COLUMN YOURCOLUMNNAME RESTART WITH XXX

where XXX = that highest value + 1.

Edit 12/08/2016: @ Quarto Die This additional info based upon your answer response.

Created Table in "embedded" DB, copied to "split" DB and executed both ALTER statements (using 0 for restart as it is new without records but 1 works also) all as stated above. This was result:

image description

Entering data, the ID always displayed 0 until the table was closed & re-opened; then existing ID's were OK but new still displayed 0.

Deleting this highlighted piece in the content.xml file:

image description

then corrected the problem and the correct auto increment value always appeared.