[AccessD] Access and SQL Server

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>>