-1

i have an id coloumn which is integer and auto incremented type.

The problem is when ever i delete a row the continuity of the number breaks.

+----------------------+----+    
| name                 | id |    
+----------------------+----+    
| mashable             |  1 |    
| Behance              |  2 |    
| Techcrunch           |  3 |    
| flipkart             |  4 |   
+----------------------+----+

FOR EXAMPLE if i delete the row with id=2, then i output in id will be 

+----------------------+----+
| name                 | id |
+----------------------+----+
| mashable             |  1 |
| Techcrunch           |  3 |
| flipkart             |  4 |
+----------------------+----+

but i want it to be like : 

+----------------------+----+
| name                 | id |
+----------------------+----+
| mashable             |  1 |
| Techcrunch           |  2 |
| flipkart             |  3 |
+----------------------+----+

How to do it ??

Aayush
  • 223
  • 1
  • 3
  • 13
  • 6
    Well, the first question is: why do you need it? Do you want it just to look nice, or do you have some strong business reason behind it? MySQL integer types can hold more than enough values: http://dev.mysql.com/doc/refman/5.0/en/integer-types.html – biziclop Sep 21 '13 at 06:34
  • 2
    Is ID your primary key? If so then don't do this. – Himanshu Sep 21 '13 at 06:35
  • if not primary key then how ?? and why not ? @hims056 – Aayush Sep 21 '13 at 06:39
  • not any specific work right now, but i do want to know @biziclop – Aayush Sep 21 '13 at 06:39
  • 1
    @Aayush: because life is short to worry about things like this, it doesn't worth being [OCD](http://en.wikipedia.org/wiki/Obsessive–compulsive_disorder) about it. – biziclop Sep 21 '13 at 06:39
  • If you want to set the _display order_ for records, use another column that you can adjust. Don't use a column that's under `AUTOINCREMENT` and **absolutely never** use the primary key column. – Burhan Khalid Sep 21 '13 at 07:01

1 Answers1

2

To directly answer your question, here's how you fix those gaps in sequential numeric fields: Fixing gaps in mysql table row id after we delete some of them

But let's be careful here for a moment.

Let's assume id is your primary key. ID's are usually the point of reference to an object, because auto-generated ID's are unique. Call it a convention.

That means that If ANY part of your code depends on the id column, your application will break.

If you NEED to do this, then use some other field as main reference. Perhaps an unique name field or something similar.

If ID is NOT your primary key, then you probably should've chosen another name for it to begin with. Anyway, in this case, the chances of you breaking anything are much smaller.

Notice that I said smaller, but not zero. We don't know your application, so it's possible that your code uses id for something important, and that'll mean trouble for you.

Community
  • 1
  • 1
victorantunes
  • 1,141
  • 9
  • 20
  • Take is as non primary key field, Lets assume its a number field instead of id, what's the difference can it make ? – Aayush Sep 21 '13 at 06:45
  • thats what i am asking, if my field name is `number` and now it is not primary then how can i make it work that way ? – Aayush Sep 21 '13 at 06:55