Auto Increment of a column

[gcjm]

I needed a column (named "ident") in a data table as an identifier.
This column was not there at first.
After I had created the column, I numbered the rows ascending using replace.
Then I edited the "ident" field in the data workspace by checking "Auto increment".

If I now add a new record, "ident" is set to 1, but the value of the highest number plus one should be taken.
What else do I need to set?

Grüße
Georg

[yvonne.milne]

Hi Georg,

When you check 'Auto increment' in the Table Designer, it applies the AUTOINC Column Constraint this is an internal counter specific to that column and starts from 1 (* see below) for the next record to be added regardless of the current contents of existing records.

* The full ALTER TABLE and CREATE TABLE SQL commands allow you to specify the NEXTVALUE: the value (not 1) to be given for the next record to be added.

So you can use ALTER TABLE to specify 1 more than your current highest value for the next record to be added:

ALTER TABLE mytable MODIFY CONSTRAINT mycolumn SET AUTOINC NEXTVALUE myvalue

Regards,

Yvonne

[gcjm]

Sorry it seems it doesn't work.

I give:
ALTER TABLE ereignis MODIFY CONSTRAINT ident SET AUTOINC NEXTVALUE 3570

My last record has the value 3569 in the ident column and
I want the records to continue counting incrementally + 1 for new records.
So if I do
append blank
(in the workspace console) and expect ident=3570. In reality I got, ident = 1

Georg

[yvonne.milne]

Hi Georg,

Drop the autoinc then set it again with the nextvalue:

ALTER TABLE ereignis MODIFY CONSTRAINT ident DROP AUTOINC
ALTER TABLE ereignis MODIFY CONSTRAINT ident SET AUTOINC NEXTVALUE 3570

Regards,

Yvonne

[gcjm]

Now it is ok.
I dropped the autoinc for ident too before... but via via menu

Thankful
Georg