CODEWITHSUNDEEP
phpmysqldatabase
Sn0wy
Sn0wy

Reputation: 107

MySql adaptive IDs

I have a table set up with an auto increasing ID. Lets say I have the IDs 1, 2, 3, 4 and 5. When I remove ID number 3, I want ID 4 to drop down to 3, and ID 5 to drop down to 4.

Is this possible? How is this done?

Upvotes: 2

Views: 69

Answers (2)

ilter
ilter

Reputation: 4079

I think you want to keep your record IDs in order and don't want any holes between them :)

That is of course possible but you should really keep yourself away from a solution like that. If you, later on, begin relating your tables to one another, you'll regret that idea and get crazy keeping track of all the tables' foreign IDs.

Really, it really is not a bad thing having holes between records. Also, that is how the actual db logic works. When an ID is used, it's done. You won't use it again. When it's not there, you'll understand that it's gone. Databases for that matter keep track of the last used number (for the ID column) and auto increment not the last existing record's ID, but instead the last used record's ID.

In short, you can. But you won't want it.

I deliberately didn't explain how it's done. Before that wanted to make sure that is what OP really wants.

As for OP's comment It is what I really want :) There will be no other tables connected to it. I can think of a simple solution which won't require ANY database interactions, becouse OP is taking responsibility now.

Create your table without an auto-incremented ID field. Your ID field should be type int. When you're creating a record, get the last ID from the table by ordering IDs desc (from bigger to lower) and get TOP 1 from it. A sample query for your request:

SELECT TOP 1 ID FROM Table ORDER BY ID DESC

Use the result + 1 in your new INSERT query.

When you delete a value you should iterate over all your records and update their IDs one by one in a for loop. That means, if you have thousands of records, and not using the inner functions of your DB System (here you can't because you're using your own logic), it can take a while (You've been warned!). And that is, if you don't have any relations to other tables. If you do, you have to repeat that iteration for the related tables in order to keep the Foreign ID relationships (which is not maintained by the DB anymore). Because, by defying DB's inner logic, you took control to your hands and now it's your responsibility to track and modify everything your own. Did I mention that you cannot use identity column in any of the related tables because it won't let you change the IDs to IDs that don't exist in it's related table?

As you see, it is a painful proccess which you should avoid. Until you understand what is really going on, some things may not make sense at all. But believe me, we have all been there. These systems are build with a logic behind it. No need to reinvent the wheel. When someone is telling you to not go there, you seriously might want to think about it. :)

Good Luck!

EDIT: When deleting your records, you should use server side code to get and update each row. Here is the logic. Get the total count of your table rows. Create a global variable outside the loop and count your get requests incrementing by one inside the loop. Begin the loop (which has the length of the total count of your rows). You start from the bottom (from the first row which has an ID of 1). Control every row if there is a null result from your get requests. If there is a null, skip and go next. Until you find a real record. When you get it, immediately update it's ID with the global variable you created outside the loop by sending an INSERT query to your db. Your loop will finish every row with that logic and it's done.

I don't ever write these kind of detailed answers which won't help anyone else but OP. But you're lucky. Read this carefuly, and don't forget that you're on your own from now on.

Upvotes: 4

peter_the_oak
peter_the_oak

Reputation: 3710

One could see your task as a simple counter column that counts upwards.

If this is true, imagine this table items:

realID |  field_x | field_y
-------+----------+--------
 1001  |  bla     | blubb
 1003  |  bla2    | blubb2 
 1004  |  bla+    | blubbs

Now watch this:

select count(i2.realId) as counter, i1.field_x, i1.field_y 
from items i1 left join items i2 on i2.realID <= i1.realID

The result is:

counter | field_x | field_y
--------+---------+--------
  1     |  bla    | blubb
  2     |  bla2   | blubb2
  3     |  bla+   | blubbs

I haven't checked for typos or so, but the structure sure will work. The idea is to join the table to itself, the way that all records are counted that have a lesser or equal id than the corresponding record from the table. That's why it's counting up.

Like this, your column would be virtual only, you wouldn't have to "recount". If you would need it more permanent, you could store the records in a temporary intermediate table.

But whenever you use ranges in joins, be very careful and expect performance problems.

Upvotes: 2

Related Questions