[Solved] Key auto increment base number change unsuccessful

Creating tables and queries

[Solved] Key auto increment base number change unsuccessful

Postby Algeorge » Mon Feb 14, 2011 3:38 am

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

Re: Key autoincriment base number change unsuccessful

Postby Sliderule » Mon Feb 14, 2011 5:02 am

The SQL command:

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
Select
   MAX("ID") as "Max ID"

From "TheHome"


HSQL Documentation: http://www.hsqldb.org/doc/guide/ch09.html#alter_table-section 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.
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: Key autoincriment base number change unsuccessful

Postby Algeorge » Mon Feb 14, 2011 5:44 am

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

Re: [solved] Key autoincriment base number change unsuccessf

Postby Sliderule » Mon Feb 14, 2011 5:55 am

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
User avatar
Sliderule
Volunteer
 
Posts: 1171
Joined: Thu Nov 29, 2007 9:46 am

Re: [solved] Key autoincriment base number change unsuccessf

Postby Algeorge » Mon Feb 14, 2011 6:05 am

Fantastic; thats it!
Al.
Alistair Grant George (English OOo 3.2 on Windows XP Pro, SP2 or Linux (using both platforms))
Algeorge
 
Posts: 19
Joined: Sat Feb 12, 2011 9:33 pm
Location: Waihi Beach, New Zealand


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 5 guests