Am using the following notation to add a primary key to a table:
id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
And this works fine, but it seems to default the order of the table to the original input order of the rows, before adding this primary key. And this is not ideal for the current situation.
The order of this table is important, as it represents a menu structure, and I need the table to be ordered by parentId, as follows, before the primary key is added to the table:
| id | parentId | …
| 1 | 1 | …
| 2 | 1 | …
| 3 | 2 | …
| 4 | 2 | …
| 5 | 2 | …
This is how the table should look after the adding of the primary key column "id", but currently I can not seem to order the table by parentId before the id column is added.
I have been trying variations of the above query such as:
id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ORDER BY parentId ASC
but with no success.
The AUTO_INCREMENT column shouldn't necessarily conform to any particular order — it's just arbitrary integers. What will happen down the road, for example, when you add some new item to the menu? It will "belong" in the middle of the table, but its AUTO_INCREMENT value will be a newly assigned value, not subject to your current ordering.
Instead of worrying about the order of the AUTO_INCREMENT values, simply apply the correct
ORDER BYclause when selecting data from your table:
Even with an AUTO_INCREMENT column you have to apply an
ORDER BYclause to ensure the order of results, so you'd might as well use the one that makes the most semantic sense.