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:
- 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]
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"
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.