AccessDiscussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
Usually whenever I design any tables I use Autonumbers as the primary keys. And this works fine for me.
However, in designing this current table, I have the option of using an autonumber field again, or I can use a text field. This text field will probably be no more than 10-15 characters long and this entry is going to be unique.
So my question for all the guru's out there is :
Is it better to use autonumber fields as the primary key, even if there is another field that can serve as the primary key, but it happens to be a text field? (With the aforementioned table design, I'm going to have two indexes and they are both going to be unique)
Just wondering what the impact will be on database performance when joining on text fields instead of numbers?
Also, under what circumstances will it be okay to use a text field as the primary key? Size of the field perhaps??
Rohan. You can use a test field for a key anytime you want as long as it is unique. There are no ramifications I can think of off the cuff. I use them all the time. But what I have found is this. If you have an autonumber field, even if it is not a key field, it will for the most part still be unique. So, go ahead and have one with your text field, just don't make it part of your key and it will be there if you decided you need it.
The fact that you have this unique text field and you're going out of the way to including it in your table sounds like it has some kind of meaning. If that's so, and especially if users will ever see this field, then you do NOT want to use it instead of an autonumber field.
The autonumber field is not seen by users and its sole purpose is to associate other data in other tables to the same record.
Your text field sounds like it's being used (and seen). Stick to having them both and using the autonumber field as the primary key.
Michigan Dept. of Environmental Quality, Air Quality Division