[AccessD] Primary Key Best Practices

>Have you ever created a one table relational database? (OK, smart alecs -
that remained so.) 

Of course not, it would not be a relational db if there was not a child
table.

>Have you ever created a table that didn't have a PK? (when you were done
creating it).  

Yes, but not often.  I almost always add the ANPK whether or not I need one.
But any table with no children does not require a PK in order to exist and
contain data.  In order to select it for editing / deletion is another story
but it still does not have to contain a field, marked by the database as
being a PK, nor does it have to have a unique index on it.  ANY field(s)
with unique data (candidate key) can be used in a where clause to pull a
specific record or set of records for editing or deletion, and it does NOT
have to be marked as the PK to do so.

We all know what candidate keys are.  They are a field or set of fields
which taken together CURRENTLY contains data that is unique within that set
of fields in that table.  We all know that we can take any candidate key and
use it as a FK in a child table.  It is not marked in the parent as the PK,
but the fact that (the data in) it is unique allows us to use that as the FK
in the child table.  So in fact by this example we can have a table FULL OF
candidate keys DOZENS OF THEM if we can discover that many candidate keys.
We can intentionally not designate any one of them as the PK, yet in fact we
can use any valid candidate key as the FK (pointer back to the parent) in a
child table.  You can demonstrate that for yourself simply by doing a join
on two such tables, or even by building a where clause on such candidate
key.  The FUNCTIONALITY of the PK is obtained even though no PK is specified
in the parent table.

That is why it is important to not get hung up on the model!  A PK is an
artificial construct from the get go.  It is a useful construct but is not
required to do many of the things that some people here on the list believe
it is required for.  Given that it is an artificial construct, and given
that a surrogate ANPK can be substituted at the drop of a hat for a natural
PK, and given that the ANPK solves many problems that are created by a
natural PK, I choose in almost every case to use one.

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 John Bartow
Sent: Wednesday, July 25, 2007 6:27 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Primary Key Best Practices

OK now I think YOU'RE talking theoretically :o)

What purpose would a one table database serve? And if there is a purpose
would it even be considered relational? Sure I created spreadsheets that
could be called databases but not by me, because I always mentally preface
database with relational. I personally refer to a spreadsheet database as a
list to avoid confusion.

But all that aside I think we mostly agree on things here - it mostly
nitpicky terminology/theory that some of us don't agree on.

Yes or No:
Have you ever created a one table relational database? (OK, smart alecs -
that remained so.) Have you ever created a table that didn't have a PK?
(when you were done creating it)

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

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