| Author |
Topic |
|
swathigardas
Posting Yak Master
149 Posts |
 Posted - 2008-08-13 : 01:07:48
|
Is it necessary to maintain an identity column which automatically generates numbers in all the tables of a database. generally we maintain identiyy column to automatically generate numbers. Is it worth having the identity column for all the tables in a database. Please provide me a suitable answer for this  |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-13 : 01:17:42
|
| Its better to have your primary key as identity column as sql server will make sure its automatically populated with uniquely generated number each time and you dont have to have pain of generating it each time. |
 |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-08-13 : 01:22:33
|
| Each table should have a single column which uniquely identifies a row in the table. As Visakh says, the most convenient way of doing this in SQL Server is via an identity column. |
|
|
|
swathigardas
Posting Yak Master
149 Posts |
Posted - 2008-08-13 : 01:53:22
|
| Suppose if my table is a child table referring to another table.. in that case, how will the identity column help me |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-13 : 02:02:52
|
quote:
Originally posted by swathigardas
Suppose if my table is a child table referring to another table.. in that case, how will the identity column help me
you will have an identity column as pk of parent tableand child table will have a foreign key relationship to itchild table will also have another identity column as its primary key. |
|
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-08-13 : 02:14:23
|
| It helps you, for example, where you have update functionality to change the child table. Then you just say:UPDATE childSET blah blah.....WHERE child_id = <current id that you have>If you don't have this ID then you will need to add lots of cols to the WHERE clause to get a unique row to update. |
|
|
|
swathigardas
Posting Yak Master
149 Posts |
Posted - 2008-08-13 : 02:15:51
|
| yeah but i'll use the foreign key column for searching.. SO in this case do u think the child table having primary key column will help .The time taken to query the child table will be the same right in both the cases right |
|
|
|
swathigardas
Posting Yak Master
149 Posts |
Posted - 2008-08-13 : 02:19:03
|
| ok. So dexter u say that it would be useful when i'm updating the child table.. Hmm thats a good one.. Thanks |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-13 : 05:07:22
|
quote:
Originally posted by visakh16
Its better to have your primary key as identity column as sql server will make sure its automatically populated with uniquely generated number each time and you dont have to have pain of generating it each time.
Hi Visakh16.That's debatable depending on the data. Swathigardas, if your data for whichever table already has a handy unique column (or combination of columns that are guaranteed unique - Say a social security number for an employee) it may be better to use that as the primary key as it's a much better representation of the data that table is supposed to hold.Of course you can still use an auto generated ID as a surrogate key which I think is all Swathigardas really needs.-------------Charlie |
|
|
|
swathigardas
Posting Yak Master
149 Posts |
Posted - 2008-08-13 : 05:12:35
|
| Yeah right Transact Charlie |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-13 : 10:27:36
|
quote:
Originally posted by Transact CharlieSwathigardas, if your data for whichever table already has a handy unique column (or combination of columns that are guaranteed unique - Say a social security number for an employee) it may be better to use that as the primary key as it's a much better representation of the data that table is supposed to hold.
Except that Social Security Numbers are not unique. They are, in fact, re-used.Boycott Beijing Olympics 2008 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-13 : 10:29:54
|
quote:
Originally posted by Transact Charlie
quote:
Originally posted by visakh16
Its better to have your primary key as identity column as sql server will make sure its automatically populated with uniquely generated number each time and you dont have to have pain of generating it each time.
Hi Visakh16.That's debatable depending on the data. Swathigardas, if your data for whichever table already has a handy unique column (or combination of columns that are guaranteed unique - Say a social security number for an employee) it may be better to use that as the primary key as it's a much better representation of the data that table is supposed to hold.Of course you can still use an auto generated ID as a surrogate key which I think is all Swathigardas really needs.-------------Charlie
We mostly make use of surrogate key approach. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-13 : 10:32:21
|
quote:
Originally posted by blindman
They are, in fact, re-used.
I didn't know that.How long is the retention time before reuse?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-13 : 10:53:05
|
| I didn't know that either.Maybe that are reused in the USA but not in UK / Sweeden.Sorry - scratch my example I may have chosen a bad entity. My point was that if you have a *guaranteed* unique entity in your table (or combination of entities that is guaranteed unique), you may as well use that for the primary key because it is more meaningful. Also because you've made it a primary key it will enforce it's uniqueness.Or course, I think most organisations don't do this and use surrogate keys instead. They are easier for joining than a combined primary key certainly.-------------Charlie |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-13 : 10:54:15
|
| And as I said, I'm not advocating not using a surrogate key -- just that you don't automatically need to have it as your primary key if another makes more sense.-------------Charlie |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-13 : 11:48:34
|
quote:
Originally posted by Peso
quote:
Originally posted by blindman
They are, in fact, re-used.
I didn't know that.How long is the retention time before reuse?
E 12°55'05.25"N 56°04'39.16"
OK. I got that from Pat Phelan, who was probably around when SSN were first introduced and may have been involved on the project. However, the Social Security website says no:quote:
Q20: Are Social Security numbers reused after a person dies?A: No. We do not reassign a Social Security number (SSN) after the number holder's death. Even though we have issued over 415 million SSNs so far, and we assign about 5 and one-half million new numbers a year, the current numbering system will provide us with enough new numbers for several generations into the future with no changes in the numbering system.
http://www.ssa.gov/history/hfaq.htmlSo I'm not sure at the moment.Boycott Beijing Olympics 2008 |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-14 : 02:50:01
|
| Don't forget of course that if you've done your data modelling correctly you will already have a unique key on every row even before you add the surrogate. From that point it is up to you if you want a surrogate for convenience. Sometimes it's better, for example when there are loads of columns. Sometimes it's worse, becuase you need to join a load of tables to get stuff that would be already in the row had you not used a surrogate key. So, as ever, the answer to your question "Is it worth having the identity column for all the tables in a database" is sometimes yes, sometimes no!Oh - and if you do go the surrogate option don't forget to put a unique constraint on your 'real' PK. |
|
|
|
|
is identity column necessary