Primary Key Field size question

[Glowackattack]

Hi,
I have a database i am creating and have come across a problem, i have the primary key of the main table in my database set as a number data type, and long integer field size. Some of the numbers i will use for this field will be 10 digits, starting with an "8" (as i understand, Long integers can only be up to ~2,000,000,000).

What i want to know is, how can i fix this?? Can i have a primary key as a text field and just put a number in there?? Would there be any complications with this??

What other options do i have that you would recommend??

Thanks for help in advance.

 

[georgedwilkinson]

Don't assign meaning to any field that uses a long integer as a primary key. You should only use a long integer as a surrogate key, to absolutely guarantee uniqueness and to propagate easily throughout the system.

That is really the only recommended artificial key. If you want to make up some long 10 digit smart key, you're going to cause yourself grief and heartache. But at any rate, don't make a smart key your primary key, make it a unique index.

 

[Glowackattack]

I'm sorry but i am not sure i follow.

What you are saying is to create another primary key that is automatically assigned a number for long integer?? Not something that i am inputting as a primary key (i.e. SSN, ID#, etc.)

I think i see your point, but how do i solve my dilemma regarding my current field, i have a number that will always be an integer (never a decimal, never any alpha characters), but will be 10 digits and can start with an 8, which would make having it set as a number/long integer impossible...what settings should i have for this field so that i can still use it as a relationship between multiple tables??

I hope i am being clear on this, thanks for your help.

 

[georgedwilkinson]

You might want to ask somebody else to step in and answer this because I am quite biased about this topic. I am totally opposed to the concept of having any field that starts autonumbering with an "8", period.

If you're going to do it anyway, don't make it your primary key. Continue using the long integer field you have as your primary key which should be an autonumber, BTW.

If you need something that "looks" like it starts with an 8, just create a text box that puts an "8" with your integer field. Or create another field that increments that you add your "8" to.

 

[Glowackattack]

Hmm, I do not wish to autonumber with it starting as an 8, the user will be inputting information that can start with an 8 and be too many digits for a long integer field type.

We wont be using sucessive numbers like that, for example, we would want to input policy information after a policy has been placed for reporting purposes only, we dont want to bother inputting policy information for policies that are not proceeded with, only ones that are. So i have the primary key set up as the policy number, which wont be sucesssive. How can i make this field accept policy numbers that are 11 digits long and use that field in relationships for forms/subforms??

 

[georgedwilkinson]

Oh, well you could use a text field and use the format property to set it up the way you want it. But you should still use an artificial autonumber field as your primary key.

 

[Glowackattack]

Thats basically what i wanted to know, will i run into problems if i use a text field to create relationships for forms/subforms...

Thanks for your help, I was probably more confusing that i needed to be.

 

[georgedwilkinson]

You well may run into problems. Use the artificial key and look the text field up each time you need it.

 

[gemma-the-husky]

seriously, dont use a 10 char text key

have an autonumber key, and use that as your foreign key.

by all means have a unique identifier for the table - this way you can amend the unique ref if you need to.

 

[Glowackattack]

I see what you all mean...i'm going to have to scrap it and start from scratch. I'm pretty new with using access for what i'm doing now, and i understand the basic database flaw i created by trying to do this the way i did, really i should have known better, but i didnt.

Thanks for all your help guys.

 

[The_Doc_Man]

OK, let's step back and take a look.

If your user enters a 10-digit number that can start with an 8, what are the odds of all the numbers below that being entered as well? I hope for your sake the answer is, "They won't." If they WILL, Access might not be your database anyway, because you would quickly run out of space.

Now, I'm a pragmatist. George isn't wrong to suggest that you use an artificial key, but I'll be honest, you really COULD have used a 10-character prime key. Where this would hurt you is efficiency. But it is a matter of degree, not kind.

A table with not more that a couple of thousand entries with that 10-character PK would be chump-change. No sweat. Make that a couple of hundred thousand entries and Access would have to work like hell. Why? Because a PK involves an index that has to be read in to be searched, and fewer long PKs fit into a single 2Kb disk buffer. You can get more short PKs in the same space, implying fewer disk reads to search the index.

Now, if you wanted to track long numbers like that, two other options come to mind. First, you could make the number a DOUBLE (which tracks up to 15 digits pretty well) and just suitably format it. That's 8 bytes vs. 10 bytes. Every 2 bytes would help. But there is the other possibility, a currency field, which can go to much larger numbers depending on how you scale it, but it is a space hog to be avoided if you really don't need it. Here's the question to decide which way to go. Will you ever need to treat the number string as a number?

If there is ever a math operation, it is a number.

If you sort it and want automatic leading zeroes when the first digit(s) could be zero (i.e. this number string could be short), such that 2 sorts after 1000000000, you wanted a number. (Remember, in text mode, input digits are LEFT justified unless you take pains to correct that. So 2 + 9 spaces sorts AFTER 1 + 9 zeros.)

If the answer is yes, it must be a number, then store it as a DOUBLE and convert it with CStr() when you need to. It will sort like a number and behave like a number.

If the answer is no, store it as text and be done with it. Understand that the sort order will be text rules rather than numeric rules.

I actually agree with George that in theory, either way that number is too long to be a practical key, but for small datasets, it is less important than for long data sets. I'm not trying to confuse you, but I wanted you to know why that answer is what it is.

 

[georgedwilkinson]

I agree with George, also. There is absolutely no reason to have a 10 digit PK in Access. And there is no reason to have any key that must be able to start with a certain character.

 

[Glowackattack]

I agree with George, also. There is absolutely no reason to have a 10 digit PK in Access. And there is no reason to have any key that must be able to start with a certain character.

George, like i said in a post earlier, the key doesnt have to start with a certain character, i was just saying that it CAN start with an 8 and be 10 characters which would make it not compatible with a long integer field type (the whole reason for this post to begin with).

Doc man, Thanks for your input. I do understand exactly what you are saying, and all things aside, my db would probably work fine if i did use that 10 digit # as the key (there would probably only be ~15-20 entries a YEAR for this, so my chances of maxin it out are slim).

But, i do want to do things correctly, its been a few years since i finished my MIS degree, and basic DB fundamentals escaped me (and combine that with the fact that i havent worked with DB's since college). I understand the reasoning behind not using a field like that as a key to link (primarily in case that field ever needs to be updated, i dont want to have to redo all relationships and tables for just that one field). If i make the relationships based off non-logic keys, then updating that field would be extremely simple and done in one place and everything would still work just fine.

Thanks again for your help guys, I appreciate your input.