Text field as PK

[meyou]

Hey everyone,

I have a problem with the maximum number that we can insert in a numeric field. That field is the primary key of my table.

Can I bypass that limitation by using a text field and entering a number in it as the PK?

Sincerely,

 

[DCrake]

What number have you reached and how have you managed to reach that far?

 

[vbaInet]

Would removing the limitation not help instead?

 

[RuralGuy]

A Text field can be a PK but why not use an AutoNumber instead? Are you using the PK index to eliminate duplicates? You can set up a field other than the PK with a unique index but you are wasting resources and impacting performance to use indexes in this fashion.

 

[meyou]

Ok,

I can fix it with an autonumber actually you are right.

The Pk was a number generated by another system which will start generating number higher than 2147483647 soon, so just trying to be proactive.

Thanks,

 

[gemma-the-husky]

if you are really approaching maxlongint ie > than the number you mention, then you will have to think carefully about this. autonumbers stop at this number also -

and I am not sure that you will be able to evaluate a text numeric key above this - or at any rate, not with long number fields - so processing and managing a text field may not be so straight forward either.

 

[vbaInet]

My guess was the OP has the size of the field as Integer. But you could be right there Dave.

 

[gemma-the-husky]

My guess was the OP has the size of the field as Integer. But you could be right there Dave.

I think so

The OP has actually quoted the maxlongint value, in his thread - so I am sure he understands many of the implications. I'm just not sure how tractable/intractable they would become.

 

[vbaInet]

Oh I didn't see the OP's final post. This site gets really slow in the afternoons (U.K. time). It's always the PHP side of the site (which is the Forum) and every other section runs smoothly. Do you think DCrake is aware Dave?

I think the OP should start considering a new ID system to avoid this problem in the very near future just like you said.

 

[Galaxiom]

Large text fields make terrible keys because Access, being case insensitive, has to compare character by character with both lowercase and uppercase.

Have the negative long integer values been used? Autonumber random will start using them if they haven't been used already.

If you really want to continue with sequential numbering then rescale the field to use them. Set up Referential Integrity with Cascaded Updates on all the fields which use the key and subtract that big number. Reset the autonumbering.

However a good design should not care about the autonumber sequence because it should never be relevant to the user.

If you really are stuck with exceeding the maximum value for a long integer then create a composite key with a long integer and a byte field. This will increase the capacity by 256 fold which should be enough.

If not then use an integer as the second field. If this 64K fold increase is not enough then ..... stop bullshiting.