[Solved] Add autoincrementing field to table

Discuss the database features

[Solved] Add autoincrementing field to table

Postby GowerMick » Sat Aug 10, 2019 6:17 pm

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?
Last edited by Hagar Delest on Thu Aug 15, 2019 10:42 am, edited 2 times in total.
Reason: Split from topic 98463: Cannot add new records [MrProgrammer]; tagged [Solved]
Mick
LibreOffice 6.0.6.2 (x64)
Oracle Report Builder
Windows 10 Home
GowerMick
 
Posts: 112
Joined: Mon Feb 28, 2011 10:19 am

Re: Add autoincrementing field to table

Postby UnklDonald418 » Sat Aug 10, 2019 11:30 pm

If the table already has data in it, use
Code: Select all   Expand viewCollapse view
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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1278
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Add autoincrementing field to table

Postby GowerMick » Mon Aug 12, 2019 4:38 pm

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. :bravo:

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 :D
Mick
LibreOffice 6.0.6.2 (x64)
Oracle Report Builder
Windows 10 Home
GowerMick
 
Posts: 112
Joined: Mon Feb 28, 2011 10:19 am

Re: Add autoincrementing field to table

Postby robleyd » Tue Aug 13, 2019 12:38 am

Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3095
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Add autoincrementing field to table

Postby GowerMick » Wed Aug 14, 2019 3:14 pm

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! :D
Mick
LibreOffice 6.0.6.2 (x64)
Oracle Report Builder
Windows 10 Home
GowerMick
 
Posts: 112
Joined: Mon Feb 28, 2011 10:19 am


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests