[Tip] Resetting the auto-increment value of a field

Discuss the database features

[Tip] Resetting the auto-increment value of a field

Postby Lazy-legs » Thu Dec 27, 2007 12:17 am

To reset the auto-increment value of a field in OpenOffice.org Base, choose Tools -> SQL, and execute the following command:

Code: Select all   Expand viewCollapse view
ALTER TABLE "table" ALTER COLUMN column RESTART WITH value


For example:

Code: Select all   Expand viewCollapse view
ALTER TABLE "contacts" ALTER COLUMN id RESTART WITH 0


This trick can come in handy when you, for example, delete all the records in a table and want to reset its primary key.
User avatar
Lazy-legs
 
Posts: 71
Joined: Mon Oct 08, 2007 1:33 am
Location: Århus-Berlin

Re: [Tip] Resetting the auto-increment value of a field

Postby Moonoo » Sun Jan 20, 2008 10:24 pm

Hiya,

Not sure if i am doing this wrong but..

I have taken your code as below, with a few adjustments to reflect my table and Col ID. The column I am trying to reset is the Primary Key and in testing I have generated around 20 records.

ALTER TABLE "Customers" ALTER COLUMN ID RESTART WITH 0


Now I want to put the Database into Production, yet I cant seem to reset the Auto Value back to 0, it carries on from 20.

I have tried to complete the SQL below but I get an error of " Syntax Error in Sq" when running it against Open Office and "Data Content could not be Loaded, Statement does not generate a result set" when running against the database (OO Bypass)

I would expect a non return of data yet it does not seem to reset the auto value.

Any ideas on whats going wrong?

Thanks

moonoo
Open Source Junkie
Debian, OO, Mozilla
User avatar
Moonoo
 
Posts: 2
Joined: Sun Jan 20, 2008 10:13 pm
Location: UK

Re: [Tip] Resetting the auto-increment value of a field

Postby DrewJensen » Mon Jan 21, 2008 8:33 pm

First - are you entering this in the SQL window "Tools > SQL"

It will not be allowed in a SQL view of a query object.

Second - why worry about it. The ID should have no real world relevance and to impart any is considered very bad design. So if you production system starts with an ID of 21 so what, all that it should be used for is a row identifier and FK as needed in relations.

The other thing is that the syntax used is ONLY allowed if they field is created as type IDENTITY - if you are using a key that is generated any other way you will need to use a ALTER SEQUENCE statement instead.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: [Tip] Resetting the auto-increment value of a field

Postby Bret Fledderjohn » Mon Jan 21, 2008 11:04 pm

Slightly off the main topic...

DrewJensen wrote:Second - why worry about it. The ID should have no real world relevance and to impart any is considered very bad design...


This may be a stupid question and I may be taking this out of context (it is Monday after all), but why would the ID be bad design to have real world relevance? I mean if you create a table for Orders and used a field called OrderID as your Primary key and chose to make it auto number, that would have a real world relevance, right? What is the draw back to that?
-Bret
User avatar
Bret Fledderjohn
 
Posts: 2
Joined: Mon Jan 21, 2008 10:30 pm
Location: Harrisburg, PA

Re: [Tip] Resetting the auto-increment value of a field

Postby DrewJensen » Mon Jan 21, 2008 11:07 pm

ok - fair question and worth an answer...but let me beg off for the moment - also let me split the last three messages to a new thread so that we don't build on the tip topic.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: [Tip] Resetting the auto-increment value of a field

Postby Moonoo » Mon Jan 21, 2008 11:35 pm

Hi,

Thanks for your help.
Your right, starting from 20 probably ain't that bad.

Instead of choosing the SQL view I tried to run the SQL from Tool -> SQL which allowed(?) me to execute SQL direct to the database or at least thats my understanding of how it happened!

Thanks again
Open Source Junkie
Debian, OO, Mozilla
User avatar
Moonoo
 
Posts: 2
Joined: Sun Jan 20, 2008 10:13 pm
Location: UK

Re: [Tip] Resetting the auto-increment value of a field

Postby DrewJensen » Mon Jan 21, 2008 11:37 pm

Ok one last tip to the tip - after resetting this it would be a good idea to run one last command in that SQL window.

SHUTDOWN COMPACT

That will free up all the space used in testing in the tables and rewrite the script file that is used to build the database on load.

After running that command you must exit base.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: [Tip] Resetting the auto-increment value of a field

Postby Villeroy » Mon Jan 21, 2008 11:44 pm

Bret Fledderjohn wrote:Slightly off the main topic...

This may be a stupid question and I may be taking this out of context (it is Monday after all), but why would the ID be bad design to have real world relevance? I mean if you create a table for Orders and used a field called OrderID as your Primary key and chose to make it auto number, that would have a real world relevance, right? What is the draw back to that?
-Bret

If you use this ID in real world communication as invoice number instead of, let's say "ASDF-20080121-BX5", then you will lose the information after reset. The invoices you already sent have the old id-numbers.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26632
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tip] Resetting the auto-increment value of a field

Postby dylan_k » Tue Oct 22, 2013 6:59 am

I got an error when I tried this, until I put the table AND column names in quotes. I thought to post here in case it might help anybody else. So, drawing from the example above, I posted something like this:

Code: Select all   Expand viewCollapse view
ALTER TABLE "contacts" ALTER COLUMN "id" RESTART WITH 0
__
I use OpenOffice v. 4 both on Windows and Mac
User avatar
dylan_k
 
Posts: 2
Joined: Mon Oct 07, 2013 1:17 am
Location: Baltimore, MD, USA

Re: [Tip] Resetting the auto-increment value of a field

Postby DACM » Tue Oct 22, 2013 7:26 am

dylan_k wrote:I got an error...until I put the table AND column names in quotes.

See also: Naming conventions: double-quotes or ALL_CAPS with Base
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests