Page 1 of 1

[Solved] Key auto increment base number change unsuccessful

Posted: Mon Feb 14, 2011 3:38 am
by Algeorge
Hi I do this to the auto primary key column under tools/SQL:
ALTER TABLE "TheHome"
ALTER COLUMN ID
RESTART WITH 1372

Result:
This operation is not supported in statement [ALTER TABLE "TheHome"
ALTER COLUMN ID
RESTART WITH 1372]

Re: Key autoincriment base number change unsuccessful

Posted: Mon Feb 14, 2011 5:02 am
by Sliderule
The SQL command:

Code: Select all

ALTER TABLE "TheHome" ALTER COLUMN ID RESTART WITH 1372
is supported . . . BUT . . . it must meet these conditions:
  1. The field ID must already be defined as an IDENTITY field
  2. Since you did NOT put the field name ID between double quotes, the field name must be UPPER CASE.
  3. You canNOT RESTART a value, if you attempt to set it less than or equal to ANY value already residing in your TABLE. That is to protect it from using the same value a second time.[/color]
To determine the MAXIMUM value of this field, already residing in the Table "TheHome" . . . use the following SQL:

Code: Select all

Select 
   MAX("ID") as "Max ID" 

From "TheHome"
HSQL Documentation: [url]http://www.hsqldb.org/doc/guide/ch09.html#alter_table-section[/url] wrote:
ALTER TABLE <tablename> ALTER COLUMN <columnname>
RESTART WITH <new sequence value>


This form is used exclusively for IDENTITY columns and changes the next automatic value for the identity sequence.
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: Key autoincriment base number change unsuccessful

Posted: Mon Feb 14, 2011 5:44 am
by Algeorge
In response:
1..The field "ID" is Primary key and is the field name is that whats meant condition (1)?
2..Field name is upper case
3..Yes, checked and numeric was too low, but still got failed after changing to over range value.


Finally the SQL for some unknown reports "1: Command successfully executed." but field ID values are not changed at all - very odd!

I will mark this as solved as I am sure in the future that your comments will be useful to others with similar problem.
Thanks for help.
Al.

Re: [solved] Key autoincriment base number change unsuccessf

Posted: Mon Feb 14, 2011 5:55 am
by Sliderule
Al wrote:
but field ID values are not changed at all
No CURRENT field ID values are changed. This just means for any FUTURE added records, the ID counter will restart with the value you assigned.

Let me give you an example. The default first value is zero ( 0 ). Some folks want the first value to start with 1 rather than zero.

If a user wanted to 'tie' an ID number to an pre-printed invoice number, or, cheque number, it could be done this way. Just some examples.

Sliderule

Re: [solved] Key autoincriment base number change unsuccessf

Posted: Mon Feb 14, 2011 6:05 am
by Algeorge
Fantastic; thats it!
Al.