artificially generated primary key

[Naveen]

Can some one explain the use of an artificially generated primary key such
as a sequential number which by itself has no meaning.

The only use that I can think up for this is enforcing referential integrity.
We cannot even use this for joins.

I use an existing database column or columns to come up with a primary key.
This serves me well for cases where I am inserting data into a database table
and an attempt to insert duplicate data would get detected and cancelled.

On the other hand if I were to use an artificially generated primary key
the duplicate row would automatically have the next number in the sequence
for primary key and the row would get inserted even though it already exists.

Therefore can I conclude that an artificially generated primary key has very
limited use.

I welcome your comments on the two issues I have raised here.

Thanks in advance

Naveen

[JasonL]

Naveen <thalanki_naveen@rocketmail.com> wrote in message
news:3946965f$1@news.devx.com...
>
> Can some one explain the use of an artificially generated primary key such
> as a sequential number which by itself has no meaning.

Using more than one column can slow down performance with joins, etc. Using
an existing column can sometimes provide major problems if business rules
change, etc.
check out this article for more :
http://www.sqlmag.com/Articles/Index...leID=5113&pg=2

> On the other hand if I were to use an artificially generated primary key
> the duplicate row would automatically have the next number in the sequence
> for primary key and the row would get inserted even though it already
exists.

You can still use a unique constraint (in SQL server) or a unique index (in
MS Access) on columns which need to stay unique. Then if business rules
change you can drop/delete these constraints and any tables that reference
your PK aren't affected.

HTH
-Jason

[D. Patrick Hoerter]

Naveen,

An arbitrary value applied as a key is referred to as a "surrogate key".
These values do indeed have tremendous usage in relational database systems.
For example, let's say you have a customer table, and would like to do
without some complex situation where LastName, FirstName, Street, City,
State, and Zip make up a Primary Key for your joins. You'd also like a
"CustomerID". This is a perfect candidate for a surrogate key, and can then
be used in other tables as a foreign key.

Other uses are in places like lookup or "code" tables, such as
CustomerType, or Product. Instead of using CustomerType or ProductName all
over the place, you give each value in the lookup table an arbitrary ID
value, such as CustomerTypeID, or ProductID. This prevents update anomalies
that can arise by having data values distributed all over the place, and
also has the benefit of saving disk space. An integer is a four-byte column,
where something like char, varchar, or nvarchar would waste a tremendous
amount of space, both in table and index usage.

Although not a "pure" relational database concept, the surrogate key has
a very useful and necessary place in the scheme of things.

Regards,
D. Patrick Hoerter

Naveen wrote in message <3946965f$1@news.devx.com>...
>
>Can some one explain the use of an artificially generated primary key such
>as a sequential number which by itself has no meaning.
>
>The only use that I can think up for this is enforcing referential
integrity.
>We cannot even use this for joins.
>
>I use an existing database column or columns to come up with a primary key.
>This serves me well for cases where I am inserting data into a database
table
>and an attempt to insert duplicate data would get detected and cancelled.
>
>On the other hand if I were to use an artificially generated primary key
>the duplicate row would automatically have the next number in the sequence
>for primary key and the row would get inserted even though it already
exists.
>
>Therefore can I conclude that an artificially generated primary key has
very
>limited use.
>
>I welcome your comments on the two issues I have raised here.
>
>Thanks in advance
>
>Naveen

[Keith]

I agree 100%. Why use a stupid autogenerated number while you have perfectly
good candidate columns for a primary key. May cause a tab more code to open
forms etc... Just doesn't make any sense to me ???

"Naveen" <thalanki_naveen@rocketmail.com> wrote:
>
>Can some one explain the use of an artificially generated primary key such
>as a sequential number which by itself has no meaning.
>
>The only use that I can think up for this is enforcing referential integrity.
>We cannot even use this for joins.
>
>I use an existing database column or columns to come up with a primary key.
>This serves me well for cases where I am inserting data into a database
table
>and an attempt to insert duplicate data would get detected and cancelled.
>
>On the other hand if I were to use an artificially generated primary key
>the duplicate row would automatically have the next number in the sequence
>for primary key and the row would get inserted even though it already exists.
>
>Therefore can I conclude that an artificially generated primary key has
very
>limited use.
>
>I welcome your comments on the two issues I have raised here.
>
>Thanks in advance
>
>Naveen

[JasonL]

Keith <cournek@telusplanet.net> wrote :
> I agree 100%. Why use a stupid autogenerated number while you have
perfectly
> good candidate columns for a primary key. May cause a tab more code to
open
> forms etc... Just doesn't make any sense to me ???
>
I'll reiterate and perhaps clarify my previous reply. First, often the
'perfectly good' PK candidate for a table are multiple columns, which can
cause performance to suffer (the engine has to do several scans on the
tables to find matches, and do some reading on clustered vs. non-clustered
indexes). Also the best candidate for a PK from the performance side is an
integer (which usually 'perfectly good candidates' are not.).
Also, what happens when business rules affecting a 'perfectly good
candidate' change? I'll give you an example. I had a manufacturing database
with 30 or 40 tables. The logical PK of the 'main' table was the work order
number. Almost all of the tables referenced this 'main' table so I made the
work order no the PK in main and a FK in all these others. To be efficient I
stored that value in numeric datatype since at the time the business rule
was numeric characters only. Two years later, that rule changed. The number
of tables had grown and so I had to change the datatype of the column in
about 50/60 tables. That was a pain. Since then I use what are called
'surrogate' keys (or what you've called "stupid autogenerated number").
-Jason

[D. Patrick Hoerter]

Keith,

I can see that the number of books you've read exceeds the number of
production databases you've built.

>I agree 100%. Why use a stupid autogenerated number while you have
perfectly
>good candidate columns for a primary key. May cause a tab more code to
open
>forms etc... Just doesn't make any sense to me ???

Oh, let's see...maintainability, extensibility, disk space consumption
issues, tedious queries, poor performance.....

There is nothing wrong with surrogate keys. Try writing a five-table
join where all of the tables have 10-column keys. I'll be done with my app
before you debug your join problems. Not to mention that a surrogate key as
a foreign key only costs 4 bytes per row.

Regards,
D. Patrick Hoerter

Keith wrote in message <394ade28$1@news.devx.com>...
>
>I agree 100%. Why use a stupid autogenerated number while you have
perfectly
>good candidate columns for a primary key. May cause a tab more code to
open
>forms etc... Just doesn't make any sense to me ???
>
>"Naveen" <thalanki_naveen@rocketmail.com> wrote:
>>
>>Can some one explain the use of an artificially generated primary key such
>>as a sequential number which by itself has no meaning.
>>
>>The only use that I can think up for this is enforcing referential
integrity.
>>We cannot even use this for joins.
>>
>>I use an existing database column or columns to come up with a primary
key.
>>This serves me well for cases where I am inserting data into a database
>table
>>and an attempt to insert duplicate data would get detected and cancelled.
>>
>>On the other hand if I were to use an artificially generated primary key
>>the duplicate row would automatically have the next number in the sequence
>>for primary key and the row would get inserted even though it already
exists.
>>
>>Therefore can I conclude that an artificially generated primary key has
>very
>>limited use.
>>
>>I welcome your comments on the two issues I have raised here.
>>
>>Thanks in advance
>>
>>Naveen
>

[PP]

Both Natural & Surrogate keys have their respective places in database design.
'ISBN','National Insurance', 'vehicle number plates' .... would probably
be very good natural keys as they should not be duplicated. Surrogate keys
tend to be a better choice for tables where the business rules are likely
to change.

I tend to use a combination of both when I design and generally use the following
rules of thumb (with examples):

For Natural Keys
----------------
Batch Processing:
If a lot of batch processing is going to occur then the clustering on the
natural key tends to prove to be the fastest method. Especially if the rows
are interlinked ie ORDER might have a surrogate key whilst the ORDER_ITEM
would have the OrderID,OrderItem_LineNo as a clustered PKey as an intelligent
key or a composite-surrogate key. This helps with most queries as scanning
is virtually reduced to the necessary rows.

Business Rules in Triggers:
If there are going to be a lot of nested triggers being fired then the @@identity
can prove to be too problematic. If you need to return the identity then
you will need to requery the table looking for a max value defined ironically
by a natural composite key. In OLTP you can't always rely on the max(identity
column) as other users might have inserted a number of rows and transactions
cause too much locking.


Very deep relationships:
With 9 levels of joins from the parent to the great great... grandchild then
a composite natural key will enable a query with one set of joins to reach
from
great great... grandchild to the parent. I once was called in to troubleshoot
a very slow database with simple surrogate keys on every single table. The
locks needed by a business rule were held for 20s causing too many deadlocks,
the change to natural composite key caused it to run in sub 1s. hmmm big
headache updating the schema but virtually removed all deadlocks from that
part of the database.

Hot Spots
A natural clustered key can remove the risk of hot-spots causing locking
at the end of the table. Very useful if 400 users are hammering data in and
their work load is naturally identified within the Primary key.



For Surrogate Keys
------------------
Uncertain Requirements:
If I'm certain that the client isn't certain. A job number being numeric
99.99% of the time but with a prefix on all the other occassions. Less impact
analysis is needed when the customer informs you after all the development
and testing has been signed off and a very tight deadline is looming.

Fluid Business Rules:
The comment of we will never have a product code over 4 characters this centuary.
(HA!! Is this a new millinum!) Then suddenly sales and marketing have a brain
wave.

Intergration:
Importing from third parties systems, especially where duplicates need to
be intelligently removed. Use of an identity column will always make a record
unique.



Conclusion
----------
Choosing a primary key to be:
natural(intelligent) vs surrogate
simple vs composite
I tend to opt for the solution that increases the database performance ie
speed and the lack of deadlocks and locks. There is no perfect database design
and being too dogmatic about using one method alone will not always win out.

Hope the above has helped you.






"Naveen" <thalanki_naveen@rocketmail.com> wrote:
>
>Can some one explain the use of an artificially generated primary key such
>as a sequential number which by itself has no meaning.
>
>The only use that I can think up for this is enforcing referential integrity.
>We cannot even use this for joins.
>
>I use an existing database column or columns to come up with a primary key.
>This serves me well for cases where I am inserting data into a database
table
>and an attempt to insert duplicate data would get detected and cancelled.
>
>On the other hand if I were to use an artificially generated primary key
>the duplicate row would automatically have the next number in the sequence
>for primary key and the row would get inserted even though it already exists.
>
>Therefore can I conclude that an artificially generated primary key has
very
>limited use.
>
>I welcome your comments on the two issues I have raised here.
>
>Thanks in advance
>
>Naveen