On Dec 11, 4:01 pm, "Pat Hartman" please no wrote:
I think the answer you've gotten is that none of us can think of a good
reason to include an autonumber in a table if it is NOT the primary key.

I'm assuming that by 'primary key' you mean the SQL keyword PRIMARY
KEY rather than the logical meaning of 'primary key', on the basis
that an Autonumber only comes into existence in the physical
implementation and has no place in the logical model. I'm also
assuming that by 'us' you aren't alienating me g.

1) Clustering: as an extreme example, imagine a table with a ten-
column compound key and you wanted to cluster on just one of those
columns being a DATETIME consisting of non-unique values. In SQL
Server, if you created a clustered index solely on a column without a
unique constraint then the system would create an auto-increment
column under the hood to 'uniqueify' the values. In Jet, the only way
of influencing clustering is to use the PRIMARY KEY designation and
Jet will not allow you to create a PK solely on a column containing
non-unique values. You can do the same trick as SQL Server but you
have to explicitly create the 'uniqueifier' yourself and an Autonumber
column is a very convenient way of doing this. You'd then create a
compound PK on (datetime_col, unique_col) _in that order_ of course
(BTW how does one do this using the Access interface rather than
code?) In this scenario, the autonumber in the table is not the
primary key, though it is _part of_ the PRIMARY KEY.

2) PRIMARY KEY is arbitrary: you can put it on *any* columns (bar
those of type OLEOBJECT) in the table (unless existing values are NULL
and/or non-unique) or you can opt to omit a PRIMARY KEY completely: if
you have a unique constraint (e.g. unique index and NOT NULL
designation) on a candidate key I don't think anyone could fault you
(though they could point out that you were potentially missing out on
an opportunity for optimization). Therefore, a table with an
Autonumber column but without a PRIMARY KEY is perfectly acceptable.

I'm sure there are other reasons which smarter people than me could
come up with; perhaps a little thinking outside the box is required?

Jamie.

--