How to automatically reset autoincrement order while deleting a row in a database?

Databases
1

Hi there

Suppose I have a db design where there is field like User_Id,User_Name,user_Roll
User_Id—int autoincrement
User_Name varchar
User_Roll varchar

Question is suppose I delete a row from my table what I want is Index of Other rows should automatically adjust according to that row(which is deleted)

suppose I have delete a row with Index 4 then all rows below that row should decrement their index by 1

How can I acheive this?

Thanks

2

lol User Roll

Anyway, no need to worry about this. Having Ids in sequential order without any being skipped has no effect on the efficiency of the database.

It might look “off” to you as a human, but to the database it knows what the Ids are and the index doesn’t care if they are in order or if any are skipped

3

lol User Roll

:sob:

4

what if I want to maintain order in column also?

5

Use ORDER BY in your query. eg.
SELECT * FROM table ORDER BY User_Id

6

Primary keys aren’t intended to be sequential just identify unique rows. If you need something to be sequential than it should reside in another column not be based on the primary key. For example, it is common to order data by when it was created. Therefore, a good approach would perhaps include adding a timestamp column for when the row was created. You could than order by that timestamp column to achieve the intended result.

2 Likes
7

Thanks @oddz and @Mittineague

8

How to automatically reset autoincrement order while deleting a row in a database?

  1. remove hands from mouse and/or keyboard

  2. step away from the computer

  3. don’t ever think of resetting auto_increments ever again

1 Like
9

You forgot relax and only worry about the truly important things :slight_smile:

1 Like
10

Can you explain it why?

11

sure

the purpose of a primary key is to be unique

if you delete a value, this does not affect the uniqueness of other values, which remain unique

12

In my case AutoIncreent field is not primary key

Primary key is some another field

13

That seems very odd to me. As I would expect a User_Id to not change.
User_Rol(e) could likely change
And I guess on rare occasion a member might change their User_Name from Bruce to Caitlyn

But most if not all code I’ve seen use User_Id (or equivalent) as a main way of identifying members.

14

In my case User_Roll is primary key

15

Well, you can name your fields as you wish, but it can’t mean what I assumed it to mean.
So every member has a unique User_Roll ?

Mind if I ask just what is it?

16

something like
90UIT101
90UIT102
90UIT103
…
…
…

always unique

17

User_Id is an auto increment column but not a primary key?

18

[quote=“vngx, post:12, topic:192534, full:true”]
In my case AutoIncreent field is not primary key[/quote]
very weird

any chance you can show us the actual table layout?

if this is mysql, do a SHOW CREATE TABLE

19

before showing table layout I wanna ask one thing

is it necessary MySQL rule that only primary key fields should be only auto-increment?

20

not sure i understand the question

you have an auto_increment column that isn’t a primary key?

well, if mysql lets you do that, then i guess you can do it

:slight_smile: