Alternative to AutoNumber

[FuzMic]

Hi friends

I have been using AutoNumber for years in the Db.tables as a primary key for linking to other tables. I remembered having read in this forum that it is not advisable to use this, why? I can see a problem of resetting it.

What is other best way to replace this with longInteger and yet act as primary key?

Also if a tableB is linked to tableA's primary key, how can i reset all tableA primary key and yet have tableB synchronized on these reset keys?

Appreciate your views.

 

[Cronk]

I doubt you read not to use an Autonumber primary key as best practice. It was probably the other way around, don't use the autonumber as key data because it can't be changed.

For example, it might be tempting to use the Autonumber PK as say a member number or invoice number because you are then hamstrung in changing this if circumstances change or the sequence is mucked up with deletions.

 

[pbaldy]

I'd be curious what you read that said it was not advisable to use autonumber as the primary key. I often do it, and I suspect it's pretty common.

What you may have read is that it isn't advisable to use the autonumber as a field visible to the users, due to the fact that it can skip numbers occasionally. I'm not sure I'd even agree with that, as long as the skipped number isn't important to your app. I have apps where I use the autonumber as a reservation number, but nobody will ever audit the data and say "hey, what happened to number 123?".

 

[RainLover]

I use Auto Number without exception.

It is the only thing I know of were I can totally say that it will not be repeated.

Never use Auto Number for anything other than a unique identifier. Primary Key.

Never allow it to be seen by the end user.

Some people disagree. The same people run into problems when writing code. That is their problem.

 

[pbaldy]

What problems writing code would you run into with a user visible autonumber? I can't recall any despite having them in several apps running for 15+ years. I agree there are dangers to using them, and more often than not I dont, but "never"?

 

[Galaxiom]

I use Auto Number without exception.

It is the only thing I know of were I can totally say that it will not be repeated.

False on two counts.

1. I have seen autonumber go wrong and repeat when the seed gets corrupted.

2. There are other means to allocate a number which are at least as reliable.

 

[RainLover]

False on two counts.

1. I have seen autonumber go wrong and repeat when the seed gets corrupted.

2. There are other means to allocate a number which are at least as reliable.

G'day Mate

We haven't crossed swords for ages.

I did not say it can't go wrong. However when and if it does I wonder what the writer of the Database has done. Or perhaps a simple electrical zap has caused problems.

There are other means. True there is. DMax plus one is an example. I did not say there wasn't other means.

Now we are not going to have one of those stoushes are we. Please for once let me express my opinion without been challenged by you.

There is nothing that I have said that in any way would mislead the OP.

 

[Galaxiom]

I did not say it can't go wrong. I did not say there wasn't other means.

Yes you did. (My emphasis added.)

It is the only thing I know of were I can totally say that it will not be repeated.

Please for once let me express my opinion without been challenged by you.

Yes, you have a right to your opinion. However when that opinion is not supported by facts then you should expect to be challenged.

 

[FuzMic]

I am amazed with the opposing views, thanks to all great guys for allowing me to see both side of the divide.

My final take is i) seed may go wrong under catastrophic events and II) we can fix it by reseeding iii) AutoNumber is a good way so is DMax + 1.

2 questions remain

1. Can i change an existing tables PrimaryKey-AutoNo to LongInt & yet maintain the RI between the link tables, if can how?
2. Is it not true that most database don't use AutoNo eg mySQL

 

[Galaxiom]

You need to be careful with DMax+1. It should be generated immediately before the record is saved otherwise it is possible that another user will get the same result. If there are many users I would hesitate to use it.

The most reliable alternative on a busy database is to keep the next number in a table. This technique has the advantage of being able to allocate the number without immediately saving the record.

Open a recordset with options to prevent other users accessing the table while the first user reads and increments the number before releasing the locks.

See recordset options dbDenyRead+dbDenyWrite

The code should also include a loop with multiple attempts to get the next number so that it can try again if it is busy with another user.

You can simply redefine an Autonumber field to LongInteger. I don't think it affects the relationship.

Database servers have an autonumber. It defined by the IDENTITY property of the column in MS SQL Server.

 

[RainLover]

Is it not true that most database don't use AutoNo eg MySQL

MySQL does use Auto Number. I think if you upsize from Access you loose that and have to go back and fix them.

What MySQL does not have is Relationships with Referential Integrity. You have to insert the Foreign key manually.

Is this correct Glaxiom.

 

[RainLover]

What problems writing code would you run into with a user visible autonumber? I can't recall any despite having them in several apps running for 15+ years. I agree there are dangers to using them, and more often than not I dont, but "never"?

What problems writing code would you run into with a user visible autonumber?

Mate I have no idea so I can't help you with that one.

I can't recall any despite having them in several apps running for 15+ years. I agree there are dangers to using them, and more often than not I dont, but "never"?[/

You have me confused with your use of pronouns. Especially "them". So once again I can't help. Perhaps you might like to re write your post. You may have been in a hurry when you wrote this.

 

[Guus2005]

Here's my €0.01

In addition to previous comments:

When you consolidate two databases with tables having a autonumber key field as a PK in both databases might prove difficult.
In that case i have converted specific autonumber fields to GUIDs. a GUID should be unique on this globe. A GUID is about 32 characters long and can be created using a function. Use this as a PK instead of the autonumber fields.

When you don't need to consolidate (in the near future) i always use the autonumber fieldtype without a problem.

Share & Enjoy!
Guus

 

[RainLover]

Guss

I don't know the first thing about GUID, but I have the feeling that I should.

 

[ButtonMoon]

What MySQL does not have is Relationships with Referential Integrity. You have to insert the Foreign key manually.

MySQL does support Referential Integrity using the InnoDB storage engine (which is the default storage engine for new installations).

FuzMic, to the extent that there is a real difference of opinion here, the difference rests largely on different understandings of the purpose of keys and assumptions about the reason for designating a primary key in a table. Unfortunately the term "primary key" is a bit arbitrary and overloaded so it's difficult even to begin these discussions.

Practically speaking what is beyond reasonable doubt is that users of a database need a means of accurately identifying information in tables. The attribute or set of attributes they use to do that (unique identifiers) are what we define as keys in database tables.

Numbers don't always make the most user-friendly keys and nor do they address every business requirement. If your business users need to identify things by unique user names, or vehicle registrations or other alphanumeric identifiers then an autonumber column as the only unique identifier in a table isn't going to help them. That's ok because tables can even have more than one key if you want to - you just need to ensure that all the desired keys are uniquely indexed. When you have several keys in a table they are known as alternate keys or secondary keys or candidate keys. A key that is hidden from users and is internal to the database is called a surrogate key.

Some disadvantages of using an autonumber for user-visible keys have already been mentioned. People find arbitrary numbers harder to work with and remember than more "meaningful" identifiers. Numbers don't necessarily match business requirements that require other uniqueness constraints to be enforced. Autonumbers may not meet user expectations because they may have gaps and cannot easily be updated. Since you can only use an autonumber once per table it is often used purely as a surrogate key and by implication some alternative key would therefore be used by the actual users of the database.

Hope this helps.

 

[CJ_London]

with regards Access, I use autonumber fields for most PK situations, but sometimes a single column table of say 'status' which is used as a lookup to populate another table can just use the status as a primary key, although really all you need is for it to be indexed and duplicates not allowed - benefit of using it as PK is it is also the only time I cascade update related fields.

But back to autonumbers, I often use random, and on occasion ReplicationID (which is like a GUID), rather than increment. I use random where I am providing an open source solution and don't want users to be able to rely on the PK for anything other than a record identifier. ReplicationID is a hangover from 2003 and earlier days when you could replicate and synchronise data. I still develop some applications where users need a copy of the db (or part of it) offsite with no or slow internet access and it makes synchronising very easy - and because I've written the sync code it is quicker and more flexible than the 2003 facility

 

[pbaldy]

What problems writing code would you run into with a user visible autonumber?

Mate I have no idea so I can't help you with that one.

Curious, as you're the one who said it:

Never use Auto Number for anything other than a unique identifier. Primary Key.

Never allow it to be seen by the end user.

Some people disagree. The same people run into problems when writing code. That is their problem.

 

[AccessBlaster]

... I remembered having read in this forum that it is not advisable to use this, why? I can see a problem of resetting it.

You may be thinking of a "Natural Key" such as a Social Security number.

Natural key
From Wikipedia, the free encyclopedia
In relational model database design, a natural key is a key that is formed of attributes that already exist in the real world. For example, a US citizen's social security number could be used as a natural key. In other words, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called domain key.

The main advantage of a natural key over a surrogate key, which has no meaning outside the database environment, is that it already exists; there is no need to add a new, artificial column to the schema. Using a natural key (when one can be identified) also simplifies data quality: It ensures that there can only be one row for a key; this "one version of the truth" can be verified, because the natural key is based on a real-world observation.

The main disadvantage of choosing a natural key is that its value may change and the relational database engine may not be able to propagate that change across the related foreign keys. For example, if person_name is used as the primary key for the person table, and a person gets married and changes name, then all of the one-to-many related tables need to be updated also. The secondary disadvantage of choosing a natural key is identifying uniqueness. The primary key must consist of the attributes that uniquely identify a row. However, it may be difficult (or it may add constraints) to create a natural key on a table. For example, if person_name is used as a primary key for the person table, many persons may share the same name and all but the first entry will be rejected as a duplication. The uniqueness constraint may be overcome by adding another column to the primary key, like street_address, to increase the likelihood of uniqueness.

 

[FuzMic]

Gala you are truly some one I can remember as a friend. Again thank you. Kind regards

 

[FuzMic]

After replying I then realise that there are more posts. I appreciate all your sharing, thank you.