1. I use identity columns all the time, unless the table is likely to have
enough rows that I might hit the limit. In Microsoft SQL Server, identity
columns are of datatype int, which means I would have to exceed 2 billion
rows. However, that's more likely than it sounds, because the dbms won't
reseed the identity column values if you delete rows.
Someone made the comment that it makes your code non-portable. That is
true to a certain extent, but in all my years of being a DBA and doing web
development, I've never needed to switch platforms. If I ever do, it will
be such a huge project that the conversion of identity columns will be the
least of my worries. It is something to consider, but it's not like you'll
be switching from Oracle to SQL Server on a weekly basis.
Whenever I need an artificial key and I can't use an identity column, I use
UUID values. MS SQL Server has a special datatype of uniqueidentifier that
you can use, and you can use the SQL function NewID() as the default value
for the column to provide identity-like functionality. If you ever have to
convert from one platform to another, you can choose to go with either the
binary or string form of the UUID data.
2. Basically, if you hit the limit of the identity column, you can't add
any more rows to the table because you'll get an error returned. While
I've never actually encountered the error, I suspect it will be a duplicate
primary key violation.
3. When none of the columns are unique, or if they are unique but might
change (those infamous "management directives" have caused that more than
once), I go to point #1 and add an artificial key to the table. Why not
just allow the unique columns to change? Laziness, complexity,
interdependent systems outside the dB. Pick any two.
|-----------------------------+-------------------------------------------|
|Eric A. Laney |Nodding the head does not row the boat. |
|Systems Engineer | |
|LAN Optimization Team | |
|Verizon Data Services | Irish Proverb|
|Voice: 813.978.4404 | |
|Pager: 888.985.8519 | |
|-----------------------------+-------------------------------------------|
|---------+--------------------------->
| | "CT, Loo" |
| | <[EMAIL PROTECTED]|
| | my> |
| | |
| | 2002-01-03 22:18|
| | Please respond |
| | to sql |
| | |
|---------+--------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: SQL <[EMAIL PROTECTED]>
|
| cc:
|
| Subject: Questions
|
>------------------------------------------------------------------------------------------------------------------------------|
Dear SQL gurus,
I have some questions on database design and hope all experts out there
can give me some ideas/advice.
Please advise/comment on the followings:-
1. Should one use the identity field as primary key in designing a
database?
If yes, isn't it has a limitation when the number reaches its
limits?
2. What would happens to the database when the identity field's value
reaches its limits?
3. Any advice on selecting a primary key for a table where none of the
columns has unique value?
Thanks in advance.
Kind regards,
CTLoo
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
