[AccessD] Primary Key Best Practices

jwcolby jwcolby at colbyconsulting.com
Wed Jul 25 20:54:00 CDT 2007


>That's cheating, John.  From an interface, you need a PK.

I think you should look at your definition of a PK.  I have already
demonstrated that even from Access you do NOT have to have a PK.  You have
to have a candidate key, and you have to lie to Access and TELL ACCESS that
some candidate key is the PK, but in fact it doesn't really have to be the
PK of the SQL Server table. 

>Why bother to be a stickler about anything if you're going to play directly
in the tables?

I do a lot of "playing" directly in the tables.  There are TONS of scenarios
where direct fiddling in the tables is required - usually by various queries
directly manipulating data.  Have you not been reading about my application
where I do all of this stuff in these huge tables and NEVER look at the data
directly.

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 9:19 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Primary Key Best Practices

That's cheating, John.  From an interface, you need a PK.  Why bother to be
a stickler about anything if you're going to play directly in the tables?

Charlotte 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, July 25, 2007 4: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




More information about the AccessD mailing list