Resetting column ID's

Question

I currently have a database which looks a lot like the one below:

1 citrus
2 pear
4 apple
5 melon
8 mango

The numbers represent a column that hold the row numbers but because rows are often deleted they get messed up quite often. With what MySQL query in PHP could I recount these rows so they would make sense again?

what do you mean with *'a column that hold the row numbers'* ? are the numbers a field that holds an id, or are they actually just indicating in what order the row was outputted ? — nl-x, Dec 11 '13 at 20:18
This is an aesthetic issue,as long as the column is autoincrementing,gaps are irrelevant. — Mihai, Dec 11 '13 at 20:21
Especially if you have dependent tables, with this as foreign key, it would be suicidal to go renumber them. — mydoghasworms, Dec 12 '13 at 05:40

score 1 · Answer 1 · answered Dec 11 '13 at 20:21

You should never re-arrange ID's in a relational database. At least not if they are to be used as a foreign key. (Which I bet they would be, otherwise what's the sense of that ID?)

Your fruits table: 1=>citrus, 2=>pear, 4=>apple, 5=>melon, 8=>mango

Consider having another table, colors holding 1=>red, 2=>yellow, 3=>green.

And now consider having a table fruit_color holding 1=>2 , 2=>3, 4=>3, 5=>2, 8=>1.

Now what would happen if I were to just go rearrange your fruits table?... The relations would get messed up.

score 0 · Accepted Answer · answered Dec 11 '13 at 20:22

The ID associated with each row is a primary key, and is generally auto_increment'd on each insert. This id is used as the unique identification for each row, so that a query can be used to select it, and it alone.

As you delete data, the rows will remain lined up in order from lowest id to highest id, the highest id being the last row inserted.

It is normal for the database to have gaps, but you can also manually assign the id, granted you know that it does not already exist in the database.

If you want to keep your data in a specific order, you could assign an index to each object, representing its rank, and using an order by command in the query.

SELECT id, fruit, rank FROM fruits ORDER BY rank ASC;

smcjones · Answer 3 · 2013-12-11T21:23:39.830

What you are trying to do is going to be (A) complicated code, and (B) a nightmare to update, which means that (C) odds are good it is going to screw up your database. As nl-x suggests, your best bet is to leave your PRIMARY KEYs as is. I would recommend, however, if you want to have a set ID, to assign it when you pull it with PHP.

function get_fruits() {
    //SQL query
    $query = ...
    //SQL result
    $i = 0;
    while($row = $query->fetch_assoc()) {
        $rows[$i] = $row;
    }

    return $rows;
 }

Now when you cycle through your rows you'll be able to treat each array key as the rank, and the end user will be none the wiser, all while keeping your database's integrity up to snuff.

** Pulling Data by Order** If for whatever reason you needed to be able to pull by the record number rather than the ID, you can also get the 5th record from the following table with the following query.

ID | fruit_name
----------------
1  | Apple
2  | Banana
4  | Kiwi
7  | Coconut
9  | Strawberry

The Query in PHP:

$sql = "SELECT fruit_name FROM fruit_table LIMIT $i,1"

That will pull the $i+1 record. Meaning if you want to get the first record, $i=0. This is the way most for loops are executed, and MySQL auto increments by default will start with 1.

Resetting column ID's

3 Answers3