Page 1 of 1
first field always a unique number.
Posted: Wed Dec 26, 2007 12:31 am
by JellyFish
Why is it that I find examples illustrate the first field in a table to be a unique number; an id?
What's the benefit of having a id like this and when are the times you shouldn't or don't really need to have it?
Posted: Wed Dec 26, 2007 4:13 am
by arjan.top
it's the point of id, so you can delete, edit or something else with WHERE id=1 and be shure it is the row you are trying to edit (of course if you choose the wrong id it is not

)
Re: first field always a unique number.
Posted: Wed Dec 26, 2007 5:39 am
by jmut
JellyFish wrote:Why is it that I find examples illustrate the first field in a table to be a unique number; an id?
What's the benefit of having a id like this and when are the times you shouldn't or don't really need to have it?
Only reason is to be unique identifier for all records. No more no less... e.g You could take social security number as ids...but then will be interesting for non-USA citizen.
Posted: Wed Dec 26, 2007 3:00 pm
by califdon
A couple of comments:
Relational database engines are all based on relational database theory that is based on mathematical set theory and was developed and published by E. F. Codd and Chris Date in the 1960's. That theory is like algebra, each theorem is based on underlying theorems. One of the most basic theorems is that every row in a relation (table) must be uniquely identified. You may ask, won't it work if I don't make sure that every row is unique? And the answer is: sometimes. In other words, the best practice is to always insure that every row will be unique, then you'll never get bit on the a** after you've put in a lot of work on a database that some day fails.
Social Security numbers are notoriously bad for use as unique identifiers, for several reasons:
1. Not everybody has one (immigrants, non-U.S. residents, babies).
2. Some people have more than one (not supposed to, but it is actually pretty common).
3. They are not unique (again, supposed to be, but mistakes have been made several times in history and there are still some duplicate SSN's out there).
[Afterthought: There is no reason that it must be the first column of a table, it's just that most experienced database designers know they are going to include a unique ID, so they put it in first so it's obvious.]
Posted: Wed Dec 26, 2007 4:10 pm
by Kieran Huggins
It should be noted that at least some DB engines include a unique auto-increment "id" field automatically. That should say something about the importance of the column. SQLite: I'm looking at you.
Besides, it costs next to nothing and, as Don pointed out, may save your proverbial arse.
Posted: Wed Dec 26, 2007 7:17 pm
by califdon
Kieran Huggins wrote:Besides, it costs next to nothing and, as Don pointed out, may save your proverbial arse.
You spell it differently than I do . . .
