a newbie question:
i am creating a table on sql server 2005 and I am wondering what the best
practices are re the ID/PK column.
Is that good to create an AutoID column and set it as the PK?
Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE
+ FEE_ID
I am struggling to understand the benefits of using an Auto ID column
because it won't prevent me to insert duplicates, right?
Thanks for ur help,
Chris*If* you decide for a surrogate key, you still have to protect the natural k
ey (UNIQUE constraint).
Very important.
Having said that, there has been endless discussions regarding whether it is
a good idea to use
surrogate keys. For this newsserver, you will manly find them in .programmin
g.
One of the benefits of using surrogate key is that the referencing tables fo
reign keys will be
smaller (one columns typically an int) compared to several columns. This can
work the other way as
well, as if you do carry the natural key to the referencing table, you might
get rid of some joins.
But this can potentially be seen as a type of not-normalized database as you
store facts redundantly
(whether of not you want to call a natural key a fact or not is open for dis
cussion, partly related
to whether the value has business meaning or not).
Another benefit of surrogate keys is that when the natural key changes (whic
h they tend to do, being
natural keys), you don't have to deal with cascading operations.
I suggest you Google and read the newsgroup archives for arguments from both
sides.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best
practices are re the
> ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_COD
E + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column beca
use it won't prevent me
> to insert duplicates, right?
> Thanks for ur help,
> Chris
>|||As Tibor mentions, this is a highly debated subject. And there are many
good, strong arguments on both sides.
It boils down to 'purity' of theory against 'utility' of practice.
Many would argue that a SSN is a 'natural' key for employee records. That
all employees must have one, and that it must be unique. Others would argue
that SSN is nothing more than a surrogate key, having absolutely nothing to
do in directly describing the entity other than being an artifact (surrogate
key) from another database system And so it goes...
I'm with Tibor on the point that using a surrogate key, often an IDENTITY
field, allows greater concentrations of key=value pairs in indexes and tends
to increase performance.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBJPYXfzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> *If* you decide for a surrogate key, you still have to protect the natural
> key (UNIQUE constraint). Very important.
> Having said that, there has been endless discussions regarding whether it
> is a good idea to use surrogate keys. For this newsserver, you will manly
> find them in .programming.
> One of the benefits of using surrogate key is that the referencing tables
> foreign keys will be smaller (one columns typically an int) compared to
> several columns. This can work the other way as well, as if you do carry
> the natural key to the referencing table, you might get rid of some joins.
> But this can potentially be seen as a type of not-normalized database as
> you store facts redundantly (whether of not you want to call a natural key
> a fact or not is open for discussion, partly related to whether the value
> has business meaning or not).
> Another benefit of surrogate keys is that when the natural key changes
> (which they tend to do, being natural keys), you don't have to deal with
> cascading operations.
> I suggest you Google and read the newsgroup archives for arguments from
> both sides.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
> news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
>|||Christophe Leroquais wrote:
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
>
Spot on. But you'd be amazed how many people don't get it!
So if you do use an IDENTITY column (the proper name for an
auto-incrementing surrogate key in SQL Server) make sure you also
declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
constraint is semantically equivalent to UNIQUE NOT NULL so exactly
which key is declared as PRIMARY KEY is pretty unimportant. The crucial
point is that constraints exist for all the relevant keys.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant.
Just to emphasize the similarity between PK and UQ constraint: A foreign key
can refer to either a
PK column (combination) *or* s UQ column (combination). One could argue that
it is rather strange
that we have do decide that one of the candidate keys is ... more important
than the other candidate
keys and make it the PK, but that is how the SQL standard is defined.
Unfortunately, most tools expose the PK very clearly, while UQ constraints a
ren't as clearly exposed
in the tools. This can be one for the reasons why we so many times see table
s with identity columns
being the PK and no other key constraints declared. This, of course, doesn't
prevent duplicates of
the business entity at all.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1157142615.776514.268060@.m79g2000cwm.googlegroups.com...
> Christophe Leroquais wrote:
> Spot on. But you'd be amazed how many people don't get it!
> So if you do use an IDENTITY column (the proper name for an
> auto-incrementing surrogate key in SQL Server) make sure you also
> declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant. The crucial
> point is that constraints exist for all the relevant keys.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> Just to emphasize the similarity between PK and UQ constraint: A foreign
> key can refer to either a PK column (combination) *or* s UQ column
> (combination). One could argue that it is rather strange that we have do
> decide that one of the candidate keys is ... more important than the other
> candidate keys and make it the PK, but that is how the SQL standard is
> defined.
Your sir are in danger of getting a headache from your own common sense
http://racster.blogspot.com|||> Your sir are in danger of getting a headache from your own common sense
LOL!
Now, whether to take that as a compliment or insult is still for the jury to
decide. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Dassin" <steve@.nospamrac4sql.net> wrote in message
news:OEVy4FszGHA.772@.TK2MSFTNGP05.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> Your sir are in danger of getting a headache from your own common sense
> http://racster.blogspot.com
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0IC0cszGHA.4204@.TK2MSFTNGP04.phx.gbl...
sense
[vbcol=seagreen]> LOL!
> Now, whether to take that as a compliment or insult is still for the jury
to decide. :-)
Trust your instincts.
I think your common sense is telling you that the big picture of
keys/indexes
is filled with non sense
Q: What is the scope of a key?
Q: What is the scope of an index?
Just doing my job
best,
steve|||See the following address for a related discussion...
http://forums.oracle.com/forums/thr...ssageID=1473614
"Christophe Leroquais" wrote:
> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best
> practices are re the ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_COD
E
> + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
> Thanks for ur help,
> Chris
>
>
No comments:
Post a Comment