0

I have a MySQL table with these fields:

Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL UNIQUE,
ReleaseDate DATETIME(6) NOT NULL DEFAULT "1970-01-01"

The default ordering of this table is by the Id because it is the primary key. When I drop the Id column and make the Name the primary key the table is ordered alphabetically, which is what I want.

With the Id as the primary key and the Name is a UNIQUE index the table continues to remain ordered in order of increasing Id. I know I can use ORDER BY Name when displaying the table but is there a way to configure the table itself to always remain in alphabetical name order, and completely disregard the order of the Id column?

Harry
  • 2,636
  • 1
  • 17
  • 29
Naji
  • 674
  • 2
  • 14
  • 35
  • 2
    Can you please tag appropriately! You say MySQL.. but have SQL-server etc in your tags.. – Harry Feb 18 '18 at 23:06
  • Take a look at this to see if it helps : https://dba.stackexchange.com/questions/142979/ordering-of-table-is-based-on-clustered-index-or-non-clustered-primary-key – Harry Feb 18 '18 at 23:17
  • two posts you need to check : https://stackoverflow.com/questions/13779138/mysql-how-do-you-create-a-clustered-index AND https://stackoverflow.com/questions/19824928/how-to-sort-a-mysql-table-in-a-permanent-way – iSR5 Feb 18 '18 at 23:56

2 Answers2

4

There is no reason in SQL you should ever depend on the physical order of the database. Relational tables are conceptually more like unordered sets than ordered lists. Assuming a certain physical order may lead to unpredictable results.

If you always want an ordered version of your table available, you can use a view.

CREATE VIEW orderedView AS
SELECT * FROM myTable ORDER BY myColumn
nbot
  • 184
  • 7
1

In a relational database generally a table is ordered physically by the PRIMARY KEY. With this, the performance of queries is increased. So, if you want that your table stay ordered by "name", you must declare "name" like your primary key. If it is not really necessary, you should create a view.

Diego Sousa
  • 81
  • 2