jwcolby
jwcolby at colbyconsulting.com
Wed Mar 2 13:40:07 CST 2011
<< 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>> >