[AccessD] Primary Key Best Practices

Eric Barro ebarro at verizon.net
Thu Jul 26 12:31:16 CDT 2007


Charlotte,

Access developers by default have access to the tables in the database
container because by default they are the equivalent of SA in SQL server.
Hence when you develop in Access you take the point of view of not only the
user but the DBA as well. Both Jim and yourself and many others who use
Microsoft Access develop database applications have to deal with tables
don't you?

Granted, Colby is very passionate about this views and opinions, but he has
a valid point and he has provided proof.

Eric

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Thursday, July 26, 2007 9:55 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Primary Key Best Practices

John,

You tend to be highly selective about what you choose to ignore when you're
in full flaming mode.  

Only dbas and sas are allowed in SQL Server tables, not users.  This is a
developer's list.  We develop database APPLICATIONS.  That generally means
that someone interacts with the database through an application or from
code, not directly in the tables.  Both Jim and I are coming from a database
application orientation, which you are cheerfully discrediting by pointing
out that it doesn't work that way INSIDE SQL SERVER!  We are not in the same
argument, and I've grown weary of pointing that out.

Charlotte

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, July 26, 2007 9:46 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

Charlotte, c'mon.  I am not willfully misrepresenting anything. You said it
could not be done, I showed that it can.  Perhaps you are willfully not
being specific in what you are saying?

Someone states you HAVE TO HAVE A PK to edit a table in SQL Server.
That is just plain FALSE.  I DEMONSTRATED how it was false.

If you don't like that answer then demonstrate HOW IT IS TRUE, do not
attempt to claim that I am "willfully misrepresenting" something.  I do not
see you or anyone else disputing what I said, other than a whimpering "you
just misinterpreted" or "you obviously missed the point again".

Whimper all you want but don't expect me to sympathize when you do not come
up with how my examples are wrong.  I learn from this list all the time, but
to be honest there is not much to learn from "you misrepresented" or "you
obviously missed the point".

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Thursday, July 26, 2007 12:28 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Primary Key Best Practices

Ah, yes, the classic AccessD  method for conquering opposition:
willfully misinterpret the responses, deny them any validity, and
declare yourself the winner!   Widely used by Colby and by Drew on
occasion.  It amounts to "never mind if we're saying much the same thing,
you're WRONG!!"  LOL

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, July 26, 2007 5:58 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

>From Charlotte:

>>You'd still need an identity field to edit it in SQL Server.

<<No, in fact you do not!>>

>  Yes, you do.  The true purpose of a primary key is to uniquely 
> identify a
row.

No JIM, you DO NOT.

Go to SQL Server (I did!)
Create a new table - just two rows is fine (I did) Create two fields (I
did) DO NOT create any index whatsoever (I did not) Do NOT create a PK (I
did not) Open the table (I did) Enter data (I did) Move off the record so
that it stores (I did) Do a couple of more (I did) Edit records (I did)
DELETE records (I did)

LISTEN VERY CAREFULLY JIM.  YOU DO !!!!NOT!!!! HAVE TO HAVE A PK TO DO ANY
OF THE THINGS YOU MENTIONED.

I have actually done this, apparently you have not.  You can have duplicate
data, you can have unique data (with a caveat in SQL Server - see below), it
simply does NOT MATTER.  You can add, edit and delete records in a table in
SQL Server without having any keys, candidate or otherwise, and without
having any indexes.

You need to get off it for a minute and admit that while the model is nice
in fact NO RDBMS actually IMPLEMENTS the model.  I don't know about you but
I actually live in reality and it is important to understand what REALLY
HAPPENS out here in the real world.

By "your" definition - a candidate key or a PK (which has to be a candidate
key) is any field or combination of fields where the data stored in the
fields is unique within the table and can therefore be used to uniquely
identify the row.

YOU can create a table (though you may refuse to do so in order to avoiding
admitting this, but I HAVE DONE SO) where there is NO candidate key or PK,
and in fact the data in those two fields are exactly the same in some
records.  You CAN still edit data directly from inside of SQL Server.  You
can still add records (programmatically with a query), you can still delete
records (programmatically with a query), you can still edit records
(programmatically, with a query).

JIM, I HAVE DONE THIS!

You do NOT have to have a PK in order to do ADD EDIT OR DELETE rows in a
REAL LIFE record in a REAL LIFE DATABASE, and MODEL BE DAMNED!  If the model
LIES, then it is time to pay more careful attention to reality.
Models MODEL reality, not vice versa.

NOW... 

Make two rows in the table identical.  SQL Server complains (Access does not
btw).  You CAN move off of the record, storing the data.  However you cannot
delete OR EDIT either record (in SQL Server you are screwed apparently, you
CAN in Access).  You CAN still add new records though (even in SQL Server),
and you CAN edit those OTHER records as long as those other records are
unique, even in SQL Server.  

What does this prove?  SQL Server has a bug, whereas Access does not.
Any way you look at it, SQL Server has a bug, either in allowing the
identical data to store, or not allowing the data to be edited later.

Notice that:

1) The data STORED whether or not it was unique.
2) EVEN with non unique records in place, more data could be added, edited
and deleted (even in SQL Server).
3) Access could delete or modify the data, even the records where the data
is not unique, SQL could not.

Now, scream all you want Jim, but facts is facts and REAL DBMSs do NOT
require a candidate key or a PK in order to add, delete or edit the data in
a table.  BY YOUR OWN DEFINITION, a PK or candidate key is one where ALL THE
DATA in a field or set of fields is unique.  BOTH Access and SQL Server can
add records, wither the data is unique or not.  BOTH databases can add new
records whether or not there is data in the table that prevents defining a
candidate key or a PK.  ACCESS can edit data even in records where the data
is exactly the same as another record.

JIM, I HAVE DONE THIS!

You do NOT have to have a PK in order to ADD EDIT OR DELETE rows in a REAL
LIFE record in a REAL LIFE TABLE in a REAL LIFE DATABASE, and MODEL BE
DAMNED!  If the model LIES, then it is time to pay more careful attention to
reality.  Models MODEL reality, not vice versa.

YOU CLAIM that without a PK you cannot edit data in a relational database.
YOU CLAIM that a PK is by necessity a field or set of fields where the data
is unique throughout the table.  I HAVE PROVEN that your claims are not
true!

Go ahead Jim, sputter away about how reality doesn't correctly implement the
model, that is true, and that is irrelevant.  Reality is, and NO RDBMS
correctly implements the model in all aspects, and you know that.

And (to the immense relief of everyone I am sure) I am SOOOOO done with you
and your model and this discussion.

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Thursday, July 26, 2007 7:28 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

<<No, in fact you do not!>>

  Yes, you do.  The true purpose of a primary key is to uniquely identify a
row.

  You may not have indicated in the RDBMS system a single index or that any
field or fields is a "PK" for the relation, but you are still using one
anytime you add or edit the data.

  The question is: If your going to edit a row, how do you as a user
identify which row it is that you need to change?

  You use the data in the fields combined in a certain way to know that you
are editing the correct row.  It may mean that you might have to use every
field (what is sometimes called a super-key), but you do use a key even if
you have not defined one in the RDBMS.  If you cannot identify a row
uniquely, then storing the data is basically meaningless.

  This goes to the heart of the point that I was making that a "primary key"
is much more then a pointer.  It relates to the meaning of the data, not how
it's stored.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, July 25, 2007 7:56 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

Charlotte,

>You'd still need an identity field to edit it in SQL Server.

No, in fact you do not!  I have all these huge 1 table databases that I am
currently using.  They are lists of people / addresses / information about
the people.  They stand alone, at least initially.  I always create an ANPK
but...

I just created a table in SQL server consisting of two fields char(10),
text1 and text2.  I saved the table, then I opened the table and edited the
data, directly in SQL Server.  No PK, I could edit the data.  I entered new
data records, I went back and edited existing data.  I deleted rows.  I can
do any of the things that you are saying cannot be done, with out a PK, or
even an index.  Just plain old simple char() fields.  Not an index in sight,
never mind a unique index.  No PK.  I can add records, I can edit existing
records, I can delete records, directly in the table in SQL Server.

In fact my client used these huge data tables (lists of addresses) to create
name / address lists that they sold to their clients long before I ever hit
the scene.  Just one table.  They did not understand nor care about PKs.
There were no child tables so no pointer was needed to get back to the
parent.  No unique index can be created, because there are in fact
duplicates.  They create hashes in order to discover and get rid of the
duplicates in the output but there is no field, nor combination of fields
that uniquely identify a specific record.  The client uses (makes a LOT OF
MONEY) off of these tables.

Is this a database?  I can't answer that.  It is a standalone (extremely
large) table in a big iron database management system.  It generates
millions of dollars a year for the owners.  They do not in fact ever edit
it, but they could if they wanted to, at least inside of SQL Server.

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Wednesday, July 25, 2007 5:50 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Primary Key Best Practices

You're picking nits, John.  You'd still need an identity field to edit it in
SQL Server.  If the unique key exists, you have a functional PK, whether you
call it that or not. 

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, July 25, 2007 2:47 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

Any database made up of a single table would not require a PK. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.476 / Virus Database: 269.10.20/919 - Release Date: 7/26/2007
9:56 AM
 




More information about the AccessD mailing list