first field always a unique number.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

first field always a unique number.

Post 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?
User avatar
arjan.top
Forum Contributor
Posts: 305
Joined: Sun Oct 14, 2007 4:36 am
Location: Hoče, Slovenia

Post 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 :) )
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Re: first field always a unique number.

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.]
Last edited by califdon on Wed Dec 26, 2007 7:15 pm, edited 1 time in total.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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 . . . :lol:
Post Reply