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]
[Solved] Key auto increment base number change unsuccessful
[Solved] Key auto increment base number change unsuccessful
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))
Re: Key autoincriment base number change unsuccessful
The SQL command:
is supported . . . BUT . . . it must meet these conditions:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Code: Select all
ALTER TABLE "TheHome" ALTER COLUMN ID RESTART WITH 1372
- The field ID must already be defined as an IDENTITY field
- Since you did NOT put the field name ID between double quotes, the field name must be UPPER CASE.
- 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]
Code: Select all
Select
MAX("ID") as "Max ID"
From "TheHome"
I hope this helps, please be sure to let me / us know.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.
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
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.
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))
Re: [solved] Key autoincriment base number change unsuccessf
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.Al wrote:
but field ID values are not changed at all
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
Fantastic; thats it!
Al.
Al.
Alistair Grant George (English OOo 3.2 on Windows XP Pro, SP2 or Linux (using both platforms))