Set Auto_Increment to 1

[Anon]

Hi there,

Can anyone help out with some code to set the AUTO_INCREMENT to 1 for my index column.

Basically I have a table that I want to allow users to navigate through freely either by next/prev buttons or by a find query. At the momemt the query is using the row number as the idendifier because there have been deleted records and the index column is therefore out of sequence.

The navigation would work better if I could use the index column, as the prev/next buttons would then also work properly after the find query.

Anyway, I reckon that I can do this if I delete and re-insert the index column each time a record is deleted, which would guarantee a perfect sequence in the index column.

So how do I ensure that the AUTO_INCREMENT is set to one each time the index column is re-inserted? I've tried the SET_AUTO_INCREMENT() function with no luck. At the moment, each time I re-insert the index column it starts from the next available number and keeps climbing.

Any ideas????

[[deleted]]

Don't do this.

Never ever change primary key content, and don't use them for the prev next buttons.

Instead, use the LIMIT statement to get records from result-row 10,20 (LIMIT 10,10), row 50 to 70 (LIMIT 50,20)

Read this:
http://www.hvt-automation.nl/yapf/index.php?cmd=viewitem&itemid=32

[Anon]

FYI: if your database supports ANSI 92 syntax for limit, it should be in this form:

limit 10, offset 10

not limit(10,10).

Specifically, postgresql 7.3 will not support the limit(10,10) syntax according to their readme file in 7.2b3...

But vincent is spot on as re sequences. autoincrement fields are not meant to be in unbroken sequence, and trying to make them so can cost lots in computing time AND result in a scrambled data set.