Primary Keys

[mbamber]

Hi all,

I have a primary field in my table called Membership_Number. This is a Long Integer data type. Is there any reason that anything more than 10 characters is being rejected under

run time error '-2147352567 (800200009)' The value you entered for this field is invalid

TIA

 

[Galaxiom]

Long can have values from minus (2^31 - 1) to _2^31 -1).

2147483647 is the largest number that can be represented with Long.

 

[mbamber]

So why would 10 characters be rejected?

 

[mbamber]

Stupid me. That's only 10 characters! Thanks

 

[David R]

Is this actually a 'number' that you will be doing math on? Maybe you should convert it to a string, then it can be as long as you like.

 

[Cronk]

Better still IMO to have a Autonumber PK, with the MemberNo stored in a separate, unique indexed text field.

 

[David R]

While true, a unique membership number satisfies the requirements of a Primary Key just fine.

 

[Galaxiom]

While true, a unique membership number satisfies the requirements of a Primary Key just fine.

Yes but long texts strings make poor keys.

A 10+ digit text key will be be 20+ bytes. The same field will also have to be in every related table and be indexed in every table.

A Long Integer is only four bytes and is faster to index than a big text field.

So unless mbamber has more than four billion members in their database I would be using a Long Integer as the PK.

 

[David R]

*shrug* Storage is cheap, and this way his PK doesn't have to be linked to EVERY query to get the membership number. He's going to want to index it and put unique constraints on it anyway, so that saves nothing.

Access is a toy database, but even it can handle 20 bytes per record.

 

[Galaxiom]

*shrug* Storage is cheap,

Sure but there is also indexing performance to consider when records are added.

and this way his PK doesn't have to be linked to EVERY query to get the membership number.

I am a great believer in natural keys (even compound ones) for all those reasons but it has to be considered against other factors.

He's going to want to index it and put unique constraints on it anyway, so that saves nothing.

Yes, that is relevant in the table where it is the PK. But where it is an FK in all the related tables it will increase the indexing overhead.