[Solved] Add autoincrementing field to table

[GowerMick]

I had same problem as FTB2. I tried adding an autoincrementing ID as key field as outlined above, but when I save the table, the autoincrement gets turned off!
How do I add an autoincrementing field to an existing table?

[UnklDonald418]

If the table already has data in it, use

Code: Select all

ALTER TABLE "YourTableName" ALTER COLUMN "PrimaryKeyFieldName" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH  XX)

where XX = 1 + the largest Primary Key value currently in the table.
Edit then execute the command at Tools>SQL
Since commands executed at Tools>SQL are working directly to the database engine be sure to select View>Refresh Tables so that the Base front end is aware of the changes.

[GowerMick]

Thanks for pointing me in the right direction, sadly, as I use Mysql as a back end, your syntax is not correct. When I get time, I'll have a go at working out what the the correct syntax is.

LO Base hides you from the nitty gritty, and it will do me good to get down and dirty once again. The old grey cells are getting older by the day

[robleyd]

See https://dev.mysql.com/doc/refman/5.7/en ... table.html

[GowerMick]

I found the following solution worked:

1. Create a new Table blocksnew, using sql
CREATE TABLE `blocksnew` ( `ID` int(10) NOT NULL AUTO_INCREMENT, ... etc) [Copying structure of remaining field from old table]

2.Then copy old table blocks (contained existing data).
3. Paste to blocksnew appending existing data.

The paste function worked fine, despite the fact the old table had existing (but unique) data in ID field.

Not very elegant, but it worked!