[Solved] Auto increment

[rmj156248]

I was using the base application in OO and the auto increment feature worked once and I saved the database I created shut down and left. I came back to add more data to it and found the data base was deleted, so I recreated it and the field that it uses for a primary key is ID and it work before on auto increment no it does not work at all even when I delete the field and add it back an manualy set it to auto increment and click save open office sets it back to no instead of yes for auto increment. the database was just for inventory. if some one could tell me how to fix so I don't have to keep putting in the next number in line.

I also uninstalled and reinstalled it nothing else has worked.

thanks.

[Villeroy]

menu:Tools>SQL...

Code: Select all

ALTER TABLE "Your Table Name" ALTER COLUMN "ID" SET GENERATED BY DEFAULT AS IDENTITY;

menu:View>Refresh Tables

[rmj156248]

got unexpected token
Unexpected token: GENERATED in statement [ALTER TABLE "Products"ALTER COLUMN "ID" SET GENERATED]

[FJCC]

Does this work?

Code: Select all

ALTER TABLE "YourTableName" ALTER COLUMN "ID" INTEGER  GENERATED BY DEFAULT AS IDENTITY;

[rmj156248]

yes it worked but will I have to keep doing it every time I have a new database

[FJCC]

If you define the table initially to have an auto incrementing primary key, that setting should be permanent. I don't know why that wasn't working on your database.

When you close down the database, be sure to wait a few seconds before turning off the computer. Base files are complex and you should give the system time to finish all of the tasks.

[Scarsax2018]

I have imported a calc spreadsheet into Base and the ID prime key does not increment, tried the SQL command but I get "2: Primary key not allowed in statement [ALTER TABLE "Membership" ALTER COLUMN "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY]" as status.

[Scarsax2018]

Solved it by making a new DB and importing Calc data.

[UnklDonald418]

Scarsax2018, the command

Code: Select all

ALTER TABLE "Membership" ALTER COLUMN "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY

will only work on an empty table. If the table already has data in it you need to prime the number generator.

Code: Select all

ALTER TABLE "Membership" ALTER COLUMN "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH  XX)

where XX = 1 + the largest ID value currently in the table.

[Scarsax2018]

Thanks for your reply.