OK, so I just got my grade back for a project I did for school(no, I never used this forum for that project, I don't beleive in those kind of ethics) but I am not pleased with my grade. I know right off that partof my structure sucked(for lack of better word at the moment). Bu what is irritating me is the comment made by my instructor that Autnumber should not be used for PK's. So hopefully this is the start of a thread to really set things straight(at least in my head) of proper PK's. I recently read that using Autonumber for a PK violates the Relational Model(which I am in the process of reading more about now.) My goal - to know as much as possible about this by the time it's said and done with. So, start posting away guru's because I'm about to argue my project grade for a few other reasons as well, like form design.

And, if possible, list your credentials if you will that pertinent to your knowledge of RDBMS(like training). Not to be nosy, but some around here think I rock at db's due to some creations but I have no paper under the belt to say so. I just want to see a few Certified DB Admins post in favor of Autonumber PK's. OK, enough rambling..let me go find a punching bag.

Thanks in advance for the undoubted good reading that is soon to follow,
Jason

Edited by: JVanKirk on Tue Dec 14 9:39:44 EST 2004.

Jason,

I've heard this as well and would like to know why. I added this thread to my favorites and hope it's an active one.
Jason K.

Other then having been designing databases for close to 20 years I have no certs or other geegaws to justify my opinions. However, I would go back to the instructor and ask him to explain his comment. Clearly Microsoft designed Autonumbers to be used as a PK. To prove this, create a table with an autonumber but do not deisgnate a PK. Save the table and Access will prompt you if you want to assign a PK. If you choose yes the Autonumber field will be assigned as the PK.

While I'm saying that MS has always made good design choices with Access (i.e. Lookup fields), Your instructor should justify why he thinks Microsoft designed Access incorrectly.

I'd also like to know where you read about autonumber violating the relational model.

Jason,

See if this helps any. Personally, I have no formal certifications. I don't use anything but autonumber PK's. Here in the real world, I have to keep things simple and moving as quickly as possible. Deadlines to meet, etc. I've run into lots of folks that think that all PK's should actually describe the entity. Well, that being the case, you'd really need to use every field in the table, in a properly normalized db, as the PK. T'ain't happenin' McGee...at least not in any db I create. Autonumber gives me that unique value to id the record, and I can use a compound index to control uniqueness.

Good luck changing your instructor's mind.

Noah

Scott,

It was in another forum(I emailed the poster already to find out why it violates the relational model). When I read some sites about the relational modelit actually mentions the use of an "alternative key" as the PK when no other values is appropriate.

What gets me, is everything I am reading leads to make sense that an Autonumber is the way to go(other than taking up a little extra space in the db). The requirements for a PK from my research so far is, No Nulls, NEVER change, not even the slightest chance for change, and unique to the record. What the heck is better than an Autonumber field. Granted, when I have the SSN in a table I think, why not use that for the ID? I still can't answer that one the best other than to say, maybe, just maybe that number could change one day, and the Autonumber will NEVER have to change.

Noah, couldn't agree more, it's a quick efficient way to make a PK in the real world and it's not like Access is a fully relational DBMS anyway.

Jason

Noah, thanks for the link, it is bookmarked. Yeah, I doubt that I will change the instructor's mind but I got a 75 on my project which pulled my final grade down to a B. It is perturbing since I learned a lot of what I know from right here and have gotten some good returns on a few designs and then I get a 75 on my project. I hate when someone goes excactly by the book instead of taking into account of practices in the real world since this is where it all get hashed out, where we all try to make a living. Yeah, so the book say 2 apples and 2 oranges = 4 fruit and 1 fruit feeds one person. If I need to feed 5 people we're eating fruit salad.

Jason

Ahhh... SSN's as Primary Keys

Here's why not to use them:
1) The Social Security Administration may re-issue the number after the death of an individual. There is a chance that two people in your db may have the same SSN, if one is deceased. (Hey, you may need dead people in your table. It depends on your app)

2) Not everyone has a SSN -- Foreign nationals are not issued an American SSN at the border. Newborn infants don't have an SSN yet.

3) Security -- If the SSN is used as a PK, it will show up in every table that is linked to the 'people' table. I'd be a little worried if my SSN was that exposed. Also, people are loathe to give out the SSN for security reasons -- and there's no legal recourse to force them to. The Social Security card is a convenient proof of legal citizenship when you start a job, but it's not the only one.

That said, The only time I ever use anything BUT an autonumber for a primary key is when I'm designing a table that is a lookup table with very few records. In that case, I often want to control those numbers myself.

-AJ

-AJ

Here is one reason why I agree with your instructure.
http://www.utteraccess.com/forums/showflat...9167&Forum=,All_Forums,&Words=&Searchpage=0&Limit=25&Main=599105&Search=true&where=&Name=31057&daterange=&newerval=&newertype=&olderval=&oldertype=#Post599167&bodyprev=
hth.

Jason,

I suggest you do read that link from quest4, as Jerry gives his reasoning in that thread as to why you do want to use autonumbers.

Noah

You should not agree with the instructor. He (or she) is incorrect (as far as Access goes) concerning the use of autonumber as the PK. It is, by far, the best choice for PK you will find. It satisfies all of the requirements for the definition of the primary key (uniqueness, non-editability, non-assignment, etc....). You really should do more research before espousing such views.

Hello, Jerry. I understand your views, but I live in the world of ISO 9000 where we need almost total accountability. Inspectors/ auditors take a very dim view of any missing numbers, Access AN can not give that kind of stablity, it leaves blanks all over the place, we need to reuse our numbers and the DCount method which I suggested does do that to a fairly good degree. It all comes down to the situation that you are working in. If you are in a loose envirement then MS AN will probably be OK, but if you are working in an enviroment with audits and inspectors, then MS AN will never do.

Jason,
I really am curious to hear what your instructor has to say to justify his comments and the grade.

Geoffrey --
I'm going to have to back Jerry on this one.

Even given the situation you describe, where you need a sequential number, I would have two field in my table --
1) an Autonumber Primary Key, to be used throughout the database as a key field, and
2) A numeric field that would remain sequential, populated via the DMax method (or a method using a recordset. I hate DMax, it's too darn slow.)

The second a number absolutely needs to be strictly sequential, it has a meaning to the user, and as such, has lost its suitibility as a primary key.

-AJ

WHOA! There's your problem right there:

This has nothing to do with stability. If you thought that autonumbers should or will provide an exact sequential numbering system, then you were looking at them the wrong way.

This is the key issue. An autonumber has only ONE function; to provide a unique identifier for a record. For that function it does exactly what its supposed to.

If you need a sequential numbering system then use one, but don't call autonumbers unstable because they don't provide something they aren't meant to.

Then we get into the issue of whether you should use your sequential number as your PK. Most people here will say no, that the PK should not have meaning. I prefer that reasoning, but I can see that there are valid reasons to use a PK that has meaning.

Thank you for your imput AJ and Scott, your point are well made. With my method of numbering I have had no trouble with the inspectors, and I don't need two fields to make it work and it takes care of itself, which is nice. It seems like this subject always brings up a major debate almost everytime, but it is always good to get different ideas, we can all learn from them and hopefully it will make us just a little bit smarter in the long run. FYI 4U.

I beg to differ. We are ISO 9000/2000 registered and I am VERY familiar with audit trails, etc....

None of these have anything to do with the purpose of the AN. Nor does the AN conflict with any ISO or CFR 21 Part 11 requirements. The primary (no pun intended) misconception concerning the AN is that it is supposed to have meaning for people. Quite the contrary is true. The AN should NEVER even be shown to people (including the developer). It is nothing more than a way for the application to identify and link records.

The AN is the primary key, think of it as the folder in the drawer that holds the record. Does the folder have any meaning when it comes to the contents? No, neither does the AN (PK) have any meaning. It is the contents of the folder (i.e. the record) that has the meaning.

In a properly normalized data structure. Every single record is unique for all values of all fields (excluding the PK). In other words, in a properly structured table you could eliminate the single field PK and use all of the fields together as the PK. This is not practically feasible. So the best way to then uniquely identify the record is to use a unique number (numbers take up less space than any other datatype). That way, it is very easy to link child records to their parent (can you imagine having to have all of the fields from the parent table in the child table just to establish the link between the two?).

Jerry, getting ready to read the link from quest4, but on your last sentence, in a post above I mention pretty much what you said here based on my research. I feel the same that Autonumber is the best way to assign PK's in Access based on the relational model info I have read so far. As always though, I appreciate the advice you give. You are among those that I try to read and reread posts so I do have more knowledge before stating my views.

Jason

Jerry, just read the other post where you stated why you like AN for PK's. Again, I couldn't agree more. Everything I have read here leads me to use AN for PK's. This thread was started as my "more research" besides the 100 pages of relational model and PK info I printed yesterday and have started to scan through. Do you have anything from any reputable source stating advantages/disadvantages to AN as a PK in Access. Not saying you aren't in my book, but to say to the instructor, oh yeah, some guy online says AN PK's are the way to go isn't going to be too impressive. Believe me, I have done quite a bit of research on keys, I used to use the old way, SSN's and so forth before I stumbled into this forum one day, since then it's been nothing but AN's.

Thanks to all who have posted so far, it's been some good reading. Even if it doesn't do squat for my grade which was a B for the class overall anyway, it does reinforce in my mind that PK's in Access should be autonumber.

Jason

Scott, me too, I emailed her two days ago and still haven't gotten anything back yet. I asked her to break down where points were taken off for the project(table structure, form design, etc.) I know my structure was a little chewed up because I couldn't get a join table to work for data input on a form right so I ended up changing a few things. I attached the db here, it's in 200 format so I can't even open it here at work(built at home) but you all can look at it anyway. It's not too fancy as I didn't feel like spending a whole lot of time on something I have no intention of using ever(we had to use a switchboard for our main menu and I hate switchboards). Anyway, have a good day everyone.

Jason

I just had a thought on PK's having no meaning to the user. I actually use my PK field in a concactenation with "IM-" for ticket numbers. The only reason I do this is I do not need to have sequential tickets and they can be any number as long as it's unique to refer back to. Since the PK is already there I use it fromthe table to concact with the IM- text in an unbound field so the user has something to refer to when asking us a question on the status of a ticket or resolution of that ticket.
So I guess the foreseeable debate on that, is it an improper use of the PK, or is it acceptable since it will never be changed by the user and essentially has no meaning to the user other than a reference number. No one can touch it, not even the developer.

Jason