2

Can i adjust the value of an auto-incremented field in the database automatically?

I have a table called "post" which has a field called "pid" which is set to auto-increment. Posts from this table may be deleted by the user at a later time, but the auto- incremented value will not be adjusted. Is there a way to adjust the pid field everytime posts are deleted?

for eg:If i have 4 entries: pid=1,2,3,4(pid-auto-increment) Now if i delete 2, is there a way to update 3 to 2 and 4 to 3 and so on ?

user2619513
  • 21
  • 4
  • This is asked often. The auto-increment id is intended to guarantee _uniqueness_, not sequentiality. If you need sequential ordering, that is better generated at the time of output, since the auto-increment primary key is likely to affect lots of other columns. It's better not to re-order them to fill the deletion holes. – Michael Berkowski Feb 08 '14 at 13:38
  • Auto-incremented primary keys are there for a reason. If you tamper with them you risk corrupting your data integrity. In other words, you are supposed to have "holes" when you delete a record. If you need your record numbering displayed in a sequential uninterrupted order then do it in your code. –  Feb 08 '14 at 13:39
  • possible duplicate of [Using unused primary keys](http://stackoverflow.com/questions/12967588/using-unused-primary-keys) – Michael Berkowski Feb 08 '14 at 13:39
  • possible duplicate of [how to re-assign auto\_increment for every row in a mysql table using php](http://stackoverflow.com/questions/6718674/how-to-re-assign-auto-increment-for-every-row-in-a-mysql-table-using-php) – Palec Feb 08 '14 at 20:29
  • @MichaelBerkowski AFAIK it is not considered good practice, when you close a question as duplicate of another one, that is itself a duplicate. This chain is quite long in case of the question you used. – Palec Feb 08 '14 at 20:51
  • @Palec In this case though, the pointed-to question more directly answers the OP's question than the next immediate one in the chain, and arguably has comparable or better answers than the one at the end of the chain. I target most suitable answers, not the end of the dupe-chain which is often dubious (not dubious in this case) The signposts remain along the chain for whomever cares to follow them. – Michael Berkowski Feb 08 '14 at 20:55
  • @Palec Further - in this case I intentionally didn't follow to the end of the chain so the OP could follow through it to see that the answer given is consistent throughout. – Michael Berkowski Feb 08 '14 at 20:56

2 Answers2

0

Why would you need to adjust the auto-increment? Each post is uniquely identified using the pid and if that is to change, then the whole DB structure will fail. The idea of the auto-increment is based on this principle and that you don't have to worry about assigning numbers yourself.

If deleting a record is a problem, then you might want to keep it in the database and flag it as deleted. They you can use this flag to show / hide from the users.

malta
  • 858
  • 1
  • 9
  • 17
  • but is'nt that just a waste of space, not deleting the un-required posts? – user2619513 Feb 08 '14 at 13:58
  • nope... don't worry about it. Depending on volume of course but it depends on what you want to achieve. – malta Feb 08 '14 at 13:58
  • As a matter of fact most systems which do some kind of revision tasks never delete any records. They just mark records as deleted. But in your case if you don't need to take care about revisions you can simply delete records and stop worrying about the "holes". –  Feb 08 '14 at 14:15
  • But once I just delete the records, there is a problem when I am automatically trying to display the posts. Thus the emphasis on filling the "holes" – user2619513 Feb 08 '14 at 14:21
  • 1
    @user2619513 What is the problem? You always format the data from database somehow. IDs are not visible at all usually. The holes are just in the numbering of the records, purely logical, physically they don’t need to exist in the storage. No space wasted. See update to my answer. – Palec Feb 08 '14 at 19:24
  • 1
    When you display a post, you will display only what the SQL query returns. If a deleted post is not found, then do not display anything. Cannot understand why this can be a problem. – malta Feb 08 '14 at 23:00
0

Deletion from end

You can manually set AUTO_INCREMENT of a table to a specified value via

ALTER TABLE tbl AUTO_INCREMENT = val;

See Using AUTO_INCREMENT in MySQL manual.

This solves deletion from end – before adding new rows, set AUTO_INCREMENT to 0 and it will be automatically set to current maximum plus one. Newly inserted rows will occupy the same IDs as the deleted ones.

Deletion from anywhere – renumbering

It is possible to manually specify value of the field having AUTO_INCREMENT. AUTO_INCREMENT is ignored them. If you specify a value already used, unique constraint will abort the query. If you specify a value that is bigger than the current maximum, AUTO_INCREMENT automatically set to this one plus one.

If you do not want to manually renumber the records, write a script for that, nor mess with stored procedures, you can use user-defined variables:

SET @id = 0;
UPDATE tbl SET id = @id := @id + 1 ORDER BY id;
SET @alt = CONCAT('ALTER TABLE tbl AUTO_INCREMENT = ', @id + 1);
PREPARE aifix FROM @alt;
EXECUTE aifix;
DEALLOCATE PREPARE aifix;

Example use

For more info see my answer to a related question.

Warning – this may be harmful!

Usually there is no need to renumber the records. Actually it may be harmful as you may have dangling references to the old record somewhere (possibly outside the DB) and they now become valid again, which could cause confusion. This is why AUTO_INCREMENT attribute of the table is not decremented after a row is deleted.

You should just delete the unwanted records and stop worrying about the holes. They are just in the numbering of the records, purely logical, physically they don’t need to exist in the storage. No space wasted in the long time perspective. For some time the storage really has holes. You can let the DB engine get rid of them by OPTIMIZE TABLE tbl or ALTER TABLE tbl ORDER BY column.

Community
  • 1
  • 1
Palec
  • 12,743
  • 8
  • 69
  • 138