Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion 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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 7th, 2005, 11:12 PM
Authorized User
 
Join Date: Jan 2005
Location: , VIC, Australia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Thoughts on Primary keys

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
Reply With Quote
  #2 (permalink)  
Old February 8th, 2005, 03:28 PM
ru1 ru1 is offline
Authorized User
 
Join Date: Feb 2005
Location: Detroit, MI, USA.
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old February 8th, 2005, 04:16 PM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
  #4 (permalink)  
Old February 9th, 2005, 05:18 PM
Authorized User
 
Join Date: Jan 2005
Location: , VIC, Australia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
inserting records with foreign and primary keys tdaustin Classic ASP Basics 0 October 4th, 2005 12:49 AM
When to define primary keys and foregin keys? method SQL Server 2000 1 August 26th, 2005 09:14 AM
Find() With multiple primary keys busher ASP.NET 1.0 and 1.1 Professional 0 June 2nd, 2005 03:00 PM
ASP and auto-generated primary keys Steve777 Classic ASP Professional 2 May 26th, 2005 10:47 AM
Subform/Links ~ Too Many Primary Keys fdsi19 Access 1 September 30th, 2003 12:48 PM



All times are GMT -4. The time now is 01:16 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.