moderated Re: Remove the ID column in databases #suggestion


Larry Finch
 



On Thu, Feb 20, 2020 at 12:11 PM Mark Fletcher <markf@corp.groups.io> wrote:
Hi All,

In each database, there is an ID column, assigned automatically, starting from 1 and monotonically increasing for each row. It seems that this column leads to some confusion, especially for databases that are edited frequently. 

I'm thinking that maybe I should remove that row? Opinions?



Well, putting on my DBA hat, relational database theory requires that there be no duplicate rows in a database table. As you can’t control what people put into a row, the ID column assures that every row will be unique. For simple databases of a few tables with no complex joins it probably doesn’t matter, but it’s safest to have the ID column. Another rule of relational databases is that there is no intrinsic order to the rows in a table, so if you want to impose order the ID column or something similar (a date-time column, for example) is essential to have. As an example, select * from mytable is not required to return the rows in the same order every time. If order matters the SQL is select * from mytable order by ID.

Larry
--
Larry Finch

N 40° 53' 50"
W 74° 02' 55"

Join main@beta.groups.io to automatically receive all group messages.