[Solved] Key auto increment base number change unsuccessful

Creating tables and queries
Post Reply
Algeorge
Posts: 19
Joined: Sat Feb 12, 2011 9:33 pm
Location: Waihi Beach, New Zealand

[Solved] Key auto increment base number change unsuccessful

Post 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]
Last edited by Algeorge on Mon Feb 14, 2011 5:45 am, edited 1 time in total.
Alistair Grant George (English OOo 3.2 on Windows XP Pro, SP2 or Linux (using both platforms))
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Key autoincriment base number change unsuccessful

Post 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.
Algeorge
Posts: 19
Joined: Sat Feb 12, 2011 9:33 pm
Location: Waihi Beach, New Zealand

Re: Key autoincriment base number change unsuccessful

Post 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.
Alistair Grant George (English OOo 3.2 on Windows XP Pro, SP2 or Linux (using both platforms))
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [solved] Key autoincriment base number change unsuccessf

Post 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
Algeorge
Posts: 19
Joined: Sat Feb 12, 2011 9:33 pm
Location: Waihi Beach, New Zealand

Re: [solved] Key autoincriment base number change unsuccessf

Post by Algeorge »

Fantastic; thats it!
Al.
Alistair Grant George (English OOo 3.2 on Windows XP Pro, SP2 or Linux (using both platforms))
Post Reply