Index on Autonumber key column

[Pat Hartman]

And I contend that an index on such column is totally worthless and wasteful. It consumes space and slows-down inserts.

-- Please do not give that advice in Access forums. Most of the people who post here are not sophisticated developers and do not understand the underlying technology. The autonumber is simply a number generator. The default is to generate sequentially ascending numbers. Except for the bugs that have been noted, this works as expected. It is the unique index or primary key constraint that prevents the insertion of duplicates. The autonumber in and of itself does not do that. In an Access table that has an autonumber field, if it is not specified as the primary key, there are bugs that can cause it to lose its seed.

You seem to be under the impression that you can't enforce RI with a non-primary candidate key. I don't know why you think that. I know you can enforce RI against non-primary keys in Jet and you can do the same with any DBMS that supports Standard SQL, including Oracle, SQL Server and MySQL. A foreign key constraint can reference any key* and the logic and behaviour is exactly the same whether that key happens to be "primary" or not. I suggest you try it out for yourself.

I have. Since you seem to be able to, please post a working example so we can all learn something.

@Others,
I did try the example syswizard suggested and found the seed bug using A2010. Compacting resets the seed correctly. I have not run into this in practice since it is rare that I append rows with existing autonumber values and most of my BE's are SQL Server anyway.

 

[Galaxiom]

An Autonumber column as surrogate key SHOULD never have duplicates as it should always be increasing in value. And I contend that an index on such column is totally worthless and wasteful. It consumes space and slows-down inserts.

That suggestion is totally wrong. Any field, autonumber or otherwise, that requires value be unique should have a unique index. Likewise if a combination of field values needs to be unique then it should have a composite unique index enforced at the table level.

Best practice does not leave data integrity of the tables to chance.

 

[ButtonMoon]

A foreign key constraint can reference any key* and the logic and behaviour is exactly the same whether that key happens to be "primary" or not. I suggest you try it out for yourself.

I have. Since you seem to be able to, please post a working example so we can all learn something.

Pat, the error message you posted speaks for itself: "No unique index found for the referenced field". If the referenced column(s) have a unique index then you can enforce referential integrity using those columns. It doesn't have to be the primary key though. Any key will do.

 

[Pat Hartman]

You are the one who said it was wasteful to have an index on the surrogate key. Of course it can't be used effectively to relate tables if you don't give it a unique index and once you go that route, why not make it the PK? After all, its reason for existence was to provide uniqueness because there wasn't a viable candidate key!

Since the argument has now become circular, I'll give you one more chance (before I close the thread) to state your case and prove to us you actually had a legitimate question and are not just attempting to engage in rhetoric and waste our time. Even the point that the surrogate key would never be used in a where clause is incorrect. My apps use them all the time. The user chooses a "customer" from a combo but the where clause references the ID field because THAT is the primary key, NOT the field the customer thinks of as CustNum. So the query ends up being Select .. From .. Where CustID = 439. Once you add an autonumber to a table, it doesn't just become A candidate key, it becomes THE candidate key or there is no point to adding it at all. If you need a sequential number without gaps for something like checks or invoices, you are usually better off generating your own number rather than using an autonumber.

 

[ButtonMoon]

You are the one who said it was wasteful to have an index on the surrogate key.

I think you are addressing syswizard who started the thread. I didn't say an index was wasteful. I said it was usually a good idea to index a surrogate key and also that an index or constraint is required to guarantee uniqueness of a key based on a key-generator like auto-numbering. I'll let syswizard respond to the points you made because generally I agree with what you've said, except in these aspects:

Of course it can't be used effectively to relate tables if you don't give it a unique index and once you go that route, why not make it the PK? After all, its reason for existence was to provide uniqueness because there wasn't a viable candidate key!

Once you add an autonumber to a table, it doesn't just become A candidate key, it becomes THE candidate key or there is no point to adding it at all.

Those arguments don't really follow. One of the very common reasons for using a surrogate key is where stability is required for very practical reasons but the values of other keys in the table are expected to be somewhat changeable. The surrogate doesn't remove the need for the other keys however. E.F.Codd (who introduced the concept of surrogates to the relational model) says:

"Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them."

It is usually assumed that business users do not see surrogate keys (their separation from the business domain is virtually the definition of a surrogate key) and therefore those users usually will need some other key with which to identify the information that's of interest to them.

A very simple example is a table of user names. A user may have a login name which is required to be unique but may change if the user requests it. An internally generated surrogate user identifier is therefore added to mitigate some of the issues that may arise from changing a key. The surrogate is very probably the key referenced by foreign keys in other tables but both keys (login name and surrogate) are still equally important from a data integrity point of view and would be enforced by unique key constraints in the database. The choice of "primary" key in a table with more than one key is in principle of very little importance. What matters is how they are used and that they are properly implemented to ensure uniqueness is enforced.

 

[Pat Hartman]

I agree that there may be the need for additional unique indexes to enforce busines rules. I don't think I said they were not needed. The point I was trying to make is that IF you add a surrogate key (autonumber) IT should be the Primary Key to avoid certain bugs that cause Access to lose the table seed. And IF you add the surrogate key IT is used to in the relationships to other tables for consistancy. That way the candidate key (if there actually was one) becomes "data" which might be used for searching.