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

[Lazy-legs]

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

Code: Select all

ALTER TABLE "table" ALTER COLUMN column RESTART WITH value

For example:

Code: Select all

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.

[Moonoo]

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

[DrewJensen]

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.

[Bret Fledderjohn]

Slightly off the main topic...

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

[DrewJensen]

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.

[Moonoo]

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

[DrewJensen]

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.

[Villeroy]

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.

[dylan_k]

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

ALTER TABLE "contacts" ALTER COLUMN "id" RESTART WITH 0

[DACM]

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