Hi all,

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??

Cheers

Rohan

__________________
Cheers

Rohan

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.

RU1

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.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks guys.

After your input and by checking out other newsgroups, I will be using autonumbers as primary keys. It's just going to be safer!!

Cheers

Rohan