Primary Key...Autonumber or Custom?

Just a general question...
When you make a primary key is it better to use and Autonumber or a Custom one? I have for the most part used auto...easy, convienient and well I have never had a problem yet (been pretty serious on Access for about 1 year). But I just read that ref integ doesn't work on Autonumbers? which would also mean cascading update & deleting correct?
I have looked at Northwind Access sample and all except "Customer" they use Auto. Well then to make a custom Primary field wouldn't I have to do that at the form level? like "CustomPrimKey = Left([CustFirstName],2 & Left([CustLastName],2) & [PriKeySetAtAutoNum]" or something like that to get what I want. I imagine you would automate this as opposed to letting the operator manually enter them.
And then as in the Northwind sample how do you know which tables need the custom numbering and which ones don't.
As I have learned on my own some of the "basics" elude me until they come back to bite me.

Thanks

I don’t know if words like always are very useful.

But since 1996 I have been using auto-numbers for every referential link with no known issues.

Plus later if you don’t have date and time stamps for new records you can very easily find the latest ones or find out the order they were entered.

Obviously I use the increment type not the random type.

Sometime you’ll want to have the number be larger than it would be starting from 0. For example: order numbers, purchase order number, etc.

This is the procedure to use if you need to increase the number.

1. Make a copy of the table
2. Paste only the structure into a new table
3. Open the new table in design view
4. Change the auto-number field type, to number.
5. Open the new table and enter a number you want the auto-number to count from.
6. Paste the new table back into the original table using the “Append Data to Existing Table” setting
7. And there you are. Your Auto-Number will advance from that point on.
8. You will probably want to delete that record after you’re done.
9. Also if the table your trying to change is a daughter table to another table you will need to add a number in the foreign key that corresponds to one in the mother table.

As an added bonus, using auto increment numbers allow you to use a very efficient method for storing the paper trail.

You don’t have to file purchase orders, invoices or any similar documents by customer etc. you can simply file them numerically. Use your data base to look up their number, vastly reducing the amount of physical space required to save all those old records, because you don’t have to have alphabet dividers, or space allocated for them.

DanG said:

Just a general question...
When you make a primary key is it better to use and Autonumber or a Custom one? I have for the most part used auto...easy, convienient and well I have never had a problem yet (been pretty serious on Access for about 1 year). But I just read that ref integ doesn't work on Autonumbers? which would also mean cascading update & deleting correct?
I have looked at Northwind Access sample and all except "Customer" they use Auto. Well then to make a custom Primary field wouldn't I have to do that at the form level? like "CustomPrimKey = Left([CustFirstName],2 & Left([CustLastName],2) & [PriKeySetAtAutoNum]" or something like that to get what I want. I imagine you would automate this as opposed to letting the operator manually enter them.
And then as in the Northwind sample how do you know which tables need the custom numbering and which ones don't.
As I have learned on my own some of the "basics" elude me until they come back to bite me.

Thanks

Click to expand...

This is actually a matter of some debate. I suggest searching the WEB for the phrase; natural vs surrogate keys. You will find articles that discuss this issue in some detail.

Personally, I fall into the surrogate key camp, especially for Access tables since the autonumber is designed solely and specifically to serve as a primary key.

Where did you hear that referential integrity doesn't work on autonumbers? That is not true. Setting referential Itegrity to Cascading updates is a moot point when you use an autonumber key since that will never change. But Cascading Deletes work fine with autonumbers.

Thank you both for your feedback...
I believe I read that referential integrity doesn't work with autonumbers in the A97 Bible. I am glad to hear this isn't such a clean cut subject as I would hate to have put databases out there that were done incorrectly. I will be checking out your reading recommendation.

Thanks for the input JSanders it's funny I was just working on something where I was going to have to start the autonumber at a higher number.

Natural keys are keys that exist naturally in the data. They are things like SSN, CUSIP, ISBN, etc. These keys are generally created by some other system. Something like LastName and FirstName might be considered a multi-field candidate key but this combination is usually not reliably unique and so is a poor choice as a primary key. Your example of takeing parts of various fields and concatenating them is just about the worst approach possible. Keys made using this technique invariably fail at some point and the scheme needs to be violated in order to prevent duplicates. Once that happens, the field becomes unreliable.

If you don't have a specific, easily identifiable, unique candidate available use an autonumber as a surrogate. Do not make up a multi-part string to make a unique field.

I agree with Pat that you are asking for trouble using a multi-field key. But I disagree with using a natural key at all, at least not with Access. The autonumber field makes a perfect primary key. While some natural keys may seem viable as a PK, there are often problems with using them. For example it is possible for a SSN to be used and there are privacy issues involved with using them. Other keys like CUSIP and ISBN codes have to be typed in. This exposes them typos which could cause problems with related records. All such problems are eliminated with the use of an autonumber as a PK.

Thanks again for the feedback...
Yeah, I didn't thing concatenating them sounded like a great idea. So pretty much you have two ways to go...

1) custom...ssn, CUSIP and ISBN but for the most part must be manually entered which as we know is just an accident waiting to happen.
2) Autonumber...from what I can tell pretty much does the trick.

Ok, here is what started the whole thing. I had 8 indentical databases that used autonumber as a primarykey and I had to combine them to make 1 big database to get reporting info from all 8. But combining them was a challenge because of indentical primary keys, the one thing that could make these 8 database different and thereby making my job a lot easier would be to put a Region# in front of the autonumber. Region 25123..Region 25456...Region 26123...Region 26456...
That is what I did when I combined them, but if I wanted to give a prefix to the autonumber as entries were made by the user how would I do that without concantenating the two numbers.
So in the end I would love to have TransactionID# 25123 instead of ID# 123 and have it done automatically.

I am unable to have them run on one big database for many reasons. Partly because I run on a system called CITRIX. (I hear it's described as a "Thin" network system as opposed to a "Fat" system). End result is I have to log onto CITRIX to access my networked drives. Thisfrom what I can tell makes it so I can't run a FE/BE structure (which I've been dying to try). And lastly
the BOSS don't one big DBase because he's the BOSS.

So I am left with finding a better way to combine these 8 databases each quarter. And so begings the education. thanks for the reading recommendations on natural vs surrogate keys that helped a lot.

DanG said:

Thanks again for the feedback...
Ok, here is what started the whole thing. I had 8 indentical databases that used autonumber as a primarykey and I had to combine them to make 1 big database to get reporting info from all 8.

Click to expand...

Well that was your first mistake. There is no reason to use separate Tables. Using filtered queries will get the same results.

The way I would have combined the tables. Would be to create a new autonumber field in the combined table and have a Long Integer field named something like OldID. The region should be a separate field. After appending all the records into the one table you would have had a unique autonumber for each record with a corresponding old ID that would have dups.

Next you do a series of queries that joins the related tables on the old ID and Region. After testing that the joins work, turn the query into an Update query updating the FK to the new PK. Once all the queries are run all your foreign keys will be correct. You can then delete the OldID field.

DanG said:

I am unable to have them run on one big database for many reasons. Partly because I run on a system called CITRIX. And lastly
the BOSS don't one big DBase because he's the BOSS.

Click to expand...

Citrix is a Terminal services product that makes it practical to run Remote databases over a WAN. But using Citrix does NOT preclude using a FE/BE. It just means that the FE and BE will both be on the Citrix server. One of my design rules is that ANY multi-user database or a database used by someone other then the developer MUST be split. You are asking for trouble if you don't.

Another one of my rules is that users (including the Boss) DO NOT dictate database design. Users can and should dictate interface design as long as it doesn't interfere with the database design. Your Boss shouldn't know or care how your tables are setup. You are being paid for your knowledge of how best to design a database. For the Boss to dictate table structure is counter productive.

DanG, You can present the case to the pointy-haired BOSS - merge the data so that all regions link to the same back end or spend hours every quarter consolidating data for reporting.

You can certainly automate the consolidation process by making and saving the queries and then creating a sub or macro to run them when it is time to do the consolidated reporting. But there really is no easier method to the combination than what was suggested to you in the other thread.

Replication might be a solution but you need to be aware that that would change all your autonumbers to random and you would first need to consolidate the tables as was previously described so that each row has a unique id. Random autonumbers can NEVER be used for any visible use because they can be very large or very small numbers and they may be positive or negative.

Thank you both very much.
I got some #$% kicking to do...stick it to the man...and all that stuff.
I should have never let them talk me into this. I guess that's all part of the learning process.

DanG said:

Thank you both very much.
I got some #$% kicking to do...stick it to the man...and all that stuff.
I should have never let them talk me into this. I guess that's all part of the learning process.

Click to expand...

Make it a matter of economics. Does he want the productivty costs of incorrect design or does he want it to work efficiently?