Access allowing duplicate primary keys 2

[Quilmes]

Hi

I am creating primary keys using a random number generator with some other modifications. The primary keys are of type double.

I have witnessed a situation where I inserted two primary keys which appear exactly the same when I look at the table in Access, however Access allowed their insertion.

The field I am inserting into has been set to Indexed (No Duplicates), Required=Yes, 15 decimal places, default val=0.

I've tried silly things like changing required to "no".

The other thing i've tried is cutting one record's primary key out, deleting it, closing Access, opening Access, pasting this record back and it still allows this. Then i tried to type in the same number and it didn't let me.

My guess is something like the actual precision of the number is greater than what Access is displaying.

Anyone have any thoughts on this?

Thanks in advance

Quilmes
quilmes

 

[LittleSmudge]

Yes, you are right in your assumption about the cause.

Why are you going such a hard way around this problem ?

If you use an AutoNumber field set to Random instead of increment you will get a similar result automatically, but it will be an Long Integer instead of a Double - Do you REALLY need a decimal point in the middle of it ?



'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Quilmes]

Hi,

Thanks for your quick response.

I chose this method to get the advantage of the larger pool of numbers offered by Access' double type (8 bytes) as opposed to the Long which i think is only 4 bytes in Access (correct me if i'm wrong.)

I agree that it's a weird way to do it, and no i don't need the decimal point. It's just the fact that i can get more numbers that way. I will potentially have to house over a million records (although i doubt i'd use Access then!) I guess I did all this in a hurry to get it done, and then i noticed this problem.

My solution at the moment is to force the field to truncate at 15 decimal places.

Thanks again!

Quilmes
quilmes

 

[Transcend]

A long integer is access is 4 bytes - what makes you think this isn't adequate for your id?
You would have to be storing billions of records before you ran out of numbers. 4 bytes means that it can range from -2,147,483,648 to 2,147,483,647. I'm pretty sure if you ever get this number of records in access it will start falling over .. if not long before.


Transcend

 

[Quilmes]

Hi

It is adequate for now and probably for a while. This DB will be around for a while, even if and when it is migrated to a more powerful DBMS. So I was thinking about the future - allowing the greatest amount of primary keys.

Thanks

Quilmes

 

[LittleSmudge]

But 4.3 THOUSAND MILLION unique numbers !!!

HOW long do you think this database will be running ?


As and when you do port it over to a 'grown up' database you will find that an Integer Serial Number is a lot easier to port over than a Double one.

MySQL ( for example ) has an MediumInteger ( 8 bytes ) that can be set to AutoIncrement and works in the same way as the Access AutoNumber.
Porting Access AutoNumbers at 4 bytes to MySQL MediumInt is a 'no-brainer' activity because all of the existing values copy over directly with leading zeros in the first four bytes - Job Done.

Don't make life more diffecult that it is already!


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Quilmes]

Yes very good point, i suppose it's the Engineer in me wanting to generate the largest pool of random numbers i can.

Thankyou very much for your comments re: other data types to use. I will definitely be reevaluating my approach when i come to migration to a grown up db.

Quilmes

 

[Quilmes]

and thanks to everyone else who helped me in this thread. you all rock!!


quilmes