filling in primekeys through forms

Responses inline

"onedaywhen" wrote:

BruceM wrote:
NameID (primary key, or PK) - use autonumber
as the data type

Autonumber (autoincrement, IDENTITY, etc) is a property, not a data
type. A data type is also required, usually INTEGER but could be other
numeric types including, bizarrely, CURRENCY.

In table design view there is a data type column. One of the choices there
is autonumber. Number is also a choice in the data type column, as is
currency (and date/time, Yes/no, text, memo, and a few others). Integer is a
field size property for Number data type. Whether or not the terminology is
technically correct, these are the terms used within Access.

make a query
(qryNames)based on tblNames, sort the names alphabetically,

So if your suggested major use of the table is to sort on names
alphabetically, a better choice of primary key would be (LastName,
FirstName, NameID) in that order. Remember that the meaning of 'primary
key' in MS Access/Jet is different from other SQL products i.e. it
determines the physical order on disk. Building your table's physical
order on an incrementing INTEGER (or a random CURRENCY value, perhaps
g) then sorting on other columns each time you use it may not be the
best advice.

My suggested use of the Name table is to store data. My suggested use of a
query is to manipulate the data, including sorting, concatenating, limiting
to certain criteria such as date range, and so forth. One of the main
functions of a relational database is to look at data in different ways.
Even if your suggestion offers some performance advantage over use of a query
when the data is to be sorted by name, what happens when you want to sort by
some other criteria such as birth date? NameID IS a unique value. If there
are studies demonstrating that use of an autonumber (or number) PK is
ill-advised for performance reasons I would be interested in seeing them.
Until then, my own observation is that a query based on a table with several
thousand entries opens in about the same amount of time as does the table
itself.

If you want to use NameID as a foreign key in another
table then create a separate, single-column UNIQUE constraint/index.

NameID IS a unique value. What purpose is served by creating another?

Ensure you are attaching the correct meaning to 'primary key'. Also,
'FK references PK' is not a requirement: for a FK, any UNIQUE key will
do.

I am using FK to mean the field on the many side of a one-to-many
relationship. If the PK is autonumber, the FK must be number; if the PK is
other than autonumber then the FK must be of the same data type.

Also, 'FK references PK' is not a requirement: for a FK, any UNIQUE key will
do.

I cannot find where I made the statement you have placed in quotes, so I
don't know the context. A FK is defined by its relationship with a PK in the
case I have outlined. The FK is not a unique value in a one-to-many
relationship. Many records could have the same FK, but only one record on
the one side of the realationship will have that same value as a PK.

Jamie.

--

BruceM wrote:
In table design view there is a data type column. One of the choices
there
is autonumber.
Integer is a
field size property for Number data type.

If we are talking about Jet databases, there is a COUNTER data type
which is an incrementing INTEGER by implication. If you are referring
to MS Access applications, there is an autonumber feature that is
subtly different e.g. supports other numeric data types, random
progression, etc. Autonumber is exposed to Jet as IDENTITY but choosing
a data other than INTEGER will result in the loss of incrementing
functionality outside of the MS Access UI.

So the MS Access UI describes autonumber as a 'data type' and its
numeric data type as a dependent property, rather than the other way
round? Counterintuitive to me but no doubt makes sense to countless
others g. Of course, just because the MS Access UI says something it
doesn't mean it is true. To use a pertinent example, I recall seeing
this message: "A table must have a primary key for you to define a
relationship between this table and other tables in the database."
However, I can define a foreign key, which has stricter rules than an
MS Access 'relationship', using a column with a unique constraint. It
lies, it lies ...

NameID IS a unique value.

But your autonumber still doesn't provide you with an identifier. I
guess you are using an autonumber because you could have more than one
'Jean Dupont' so the it breaks duplicates. However, you will still not
be able to tell one 'Jean Dupont' row from another unless you expose
your autonumber values to users, which is a bad idea (e.g. there are
better identifiers than random/incrementing numeric values).

What purpose is served by creating another?

Let's go back to basics. If all you want is a NOT NULL UNIQUE column,
why not just declare it as NOT NULL UNIQUE? Why are you using the term
PRIMARY KEY?

If there
are studies demonstrating that use of an autonumber (or number) PK
is ill-advised for performance reasons I would be interested in
seeing
them.

Here's one you can do at home with a paper copy telephone directory.
Study exercise1: get a list of phone numbers for names beginning with
the letter A. Study exercise 2: get a list of phone numbers beginning
with the number 1 (after area codes etc). Why is exercise 1 so much
easier than exercise 2? Because the physical ordering is on name. And
why is a paper copy directory ordered on name? Because this is how most
people use the directory.

The same applies to the pages in a database table i.e. there is a
performance advantage if the physical order matches your usage. Using
an autonumber as a primary key would be like a the paper copy telephone
directory ordered by telephone number with the associated degradation
in performance.

If you are looking for something more 'official', try these:

Jet 3.0 Performance Overview (search for each appearance of the text
'cluster'):
http://www.microsoft.com/AccessDev/Articles/v3perf.htm

New Features in Microsoft Jet Version 3.0 (start reading from 'New
compacting method'):
http://support.microsoft.com/default...b;en-us;137039

ACC2000: Defragment and Compact Database to Improve Performance (key
phrase = 'Clustered Indexes'):
http://support.microsoft.com/default...b;en-us;209769

Jamie.

--