Newbie in dilemma due to OCD tries to reorder SQL database automatically

Question

Sorry, I'm very new to SQL. I just learned it few hours ago. I'm using MariaDB + InnoDB Engine with HeidiSQL software + CodeIgniter 3. Let's say I have a table named disciples with the following data:

 -------------------
| sort_id | name    |
 -------------------
|       1 | Peter   |
|       4 | John    |
|       3 | David   |
|       5 | Petrus  |
|       2 | Matthew |
 -------------------

I'm fully aware that it's better to have a column called sort_id to be able to fetch the data using ORDER BY if I prefer a custom sorting. But if I delete row 3, the new table will look like this:

 -------------------
| sort_id | name    |
 -------------------
|       1 | Peter   |
|       4 | John    |
|       5 | Petrus  |
|       2 | Matthew |
 -------------------

The thing is I'm having OCD (imagine there are 1000 rows), it hurts my eyes to see this mess with some missing numbers (in this case number 3 - see the above table) under sort_id. I think it has something to do with "relational database". Is there a way to quickly and automatically "re-assign/reset" new sort_id numbers to given rows and sort them ASC order according to the name using SQL code without having to do it manually?

 -------------------
| sort_id | name    |
 -------------------
|       1 | John    |
|       2 | Matthew |
|       3 | Peter   |
|       4 | Petrus  |
 -------------------

Is sort_id the primary key? If so, this is an incredibly bad idea. — Lynn Crumbling, Mar 09 '18 at 05:37
I read this [link](https://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key) In MySQL, the InnoDB storage engine always creates a primary key if you didn't specify it explicitly, thus making an extra column you don't have access to. — Rangka Kacang, Mar 09 '18 at 06:21
Hey, I figured this out. I created the primary key on a new column called `id` and I can now manage my OCD issue. Thanks for the heads up! — Rangka Kacang, Mar 09 '18 at 06:28

score 1 · Accepted Answer · answered Mar 09 '18 at 06:36

I figured this out after reading the answer from Lynn Crumbling.

She made me realized I need a primary key in order to have a better management for my rows which is exactly what I was looking for. It happens that InnoDB automatically creates a primary key and is hidden from HeidiSQL interface unless I specify a specific column for example id. Now, I can re-organize my table rows by editing the primary key id and the table row will automatically sort itself the way I want. Before this, I edited the sort_id but the data did not update accordingly because it was not the primary key.

 ------------------------
| id | sort_id | name    |
 ------------------------
|  1 |       1 | Peter   |
|  2 |       4 | John    |
|  3 |       5 | Petrus  |
|  4 |       2 | Matthew |
 ------------------------

Thank you.

Newbie in dilemma due to OCD tries to reorder SQL database automatically

1 Answers1