[AccessD] Access and SQL Server

Jim Dettman jimdettman at verizon.net
Wed Mar 2 15:47:06 CST 2011


John,

<<
<<  Having an auto number PK does not in any way relieve the developer from
the responsibility of 
analyzing for a field or set of fields which ensure uniqueness and setting a
unique index on those 
fields.>>

 >Yes because it's a misnomer to call a auto number a PK or even a surrogate
key.  It may be labeled 
as such, but it certainly does not perform the function of one.

What?  It certainly can.
>>

  What then is your definition of a PK?

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 02, 2011 02:40 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access and SQL Server

<<  Having an auto number PK does not in any way relieve the developer from
the responsibility of 
analyzing for a field or set of fields which ensure uniqueness and setting a
unique index on those 
fields.>>

 >Yes because it's a misnomer to call a auto number a PK or even a surrogate
key.  It may be labeled 
as such, but it certainly does not perform the function of one.

What?  It certainly can.

 >As we have discussed in the past, auto numbers are simply pointers or
tags.

Yes they are just pointers or tags.  My point is that in SQL Server you
create a field with an 
integer.  As a separate step you set it to be an auto-increment.  As another
separate step you set 
it to be the PK - SQL Server labels it as the PK of the table.  As a result
of making the field a PK 
sql server automatically creates a unique index, on that one field.

At that point you have a long auto-increment that is a PK.  At that point it
has all of the 
attributes required to be and is in fact a surrogate key.  It is indexed
unique, it is defined by 
SQL Server as the PK and it's value is automatically created by the system
with no input from the user.

It is a surrogate and it performs as one.  I use them all the time.

All of which has nothing whatsoever to do with preventing duplicate records.
The PK surrogate key 
has a unique index, however it does not prevent duplicates *records*, it
just prevents duplicate 
primary key values.  Two separate issues.

 >    As we have discussed in the past, auto numbers are simply pointers or
tags.

No, a surrogate PK is a pointer.  You can in fact have auto-numbers that are
not used as the 
surrogate PK.

John W. Colby
www.ColbyConsulting.com

On 3/2/2011 11:22 AM, Jim Dettman wrote:
> John,
>
> <<ohhhh don't go there!!!>>
>
>    Ah why not? ;)
>
> <<PKs and unique indexes are NOT the same thing.>>
>
>   I would not agree with that.  In a relational context, a PK in a
relation
> by it's very definition would form a unique index.
>
> <<  Having an auto number PK does not in any way relieve
> the developer from the responsibility of analyzing for a field or set of
> fields which ensure
> uniqueness and setting a unique index on those fields.>>
>
>    Yes because it's a misnomer to call a auto number a PK or even a
surrogate
> key.  It may be labeled as such, but it certainly does not perform the
> function of one.
>
>    As we have discussed in the past, auto numbers are simply pointers or
> tags.
>
> Jim.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, March 02, 2011 09:40 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access and SQL Server
>
>   >My question would be, how in your app do you prevent a patent from
being
> entered more then once?
>
> ohhhh don't go there!!!
>
> PKs and unique indexes are NOT the same thing.  Having an autonumber PK
does
> not in any way relieve
> the developer from the responsibility of analyzing for a field or set of
> fields which ensure
> uniqueness and setting a unique index on those fields.
>
> That "response" is one usually received from very junior DBAs.
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 3/2/2011 8:04 AM, Jim Dettman wrote:
>> Debbie,
>>
>>     I bet you use a natural key in your app without even thinking about
it
> as
>> such.  My question would be, how in your app do you prevent a patent from
>> being entered more then once?
>>
>> Jim.
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Debbie Elam
>> Sent: Tuesday, March 01, 2011 09:18 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] Access and SQL Server
>>
>> I do as well.  I have run into problems every time I have used
>> (developed by others) databases with natural keys.  I will NEVER use
>> them for the following reasons:
>>
>> 1.  Real data can ALWAYS change.  I do not care how immutable it is
>> supposed to be, data changes.  Just ran into a problem in reports out of
>> a CRM database.  One magazine has changed names 3 times in 8 years.
>> They still want info tracked together, but the natural key of a short
>> code based on the name has changed (sigh).
>> 2.  Real Data is subject to typos.  Even the best typist can realize a
>> problem happened after data has been entered.  Fix it and the
>> relationship is crap without cascade updates.
>> 3.  Real data is never as unique as you may think.  This is why natural
>> keys usually evolve into compound keys.  Had a patent database that used
>> docket numbers as a natural key.  As they supported additional
>> countries, they added country.  As addendum were added to the patent,
>> refines were added.  Now this 3 field compound key was a nightmare to
>> work with.  To top it off, you guessed it, problem 1 reared it's head
>> too.  Rare occurrence, but in a database of almost 100,000 patents, it
>> probably occurred a few times a month.  Headache every time it happened.
>>
>> Debbie
>>
>> On 3/1/2011 1:12 PM, jwcolby wrote:
> <<snip>>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list