Referential Integrity and Primary Key

[cburke06]

I'm used to using the Autonumber feature as the primary key field type in a table. However, I just read that the AutoNumber type should not be used if you want referential integrity.

If I go with a text or numeric type for my PK, won't I have to manually increment my PK for each data record?

Thanks

 

[jerseyboy]

You CAN use the autonumber for ref integrity. My understanding is every table should have some type of primary field. If you have a natural non-repeating, non-null value to use that would be better though.

Common naturally occuring primary keys:
Company Issued Employee Numbers
Student Numbers
Soc Security Number
Driver License Number
Military ID
Passports

These are all issued without EVER repeating the value.

As far as automated numbers and the like there are articles listed here on that subject. Try the Access Forum for Modules for more information. (Microsoft also has an article on custom incrementing numbers)

One warning, if you are going to have multiple people enter data concurrently you should handle that type of situation so that you don't end up with the same number, it will prompt the user "Key Validation Error"

Hope it helped,
JerseyBoy
Remember: self-praise is no recommendation

 

[WildHare]

Your key should be that logical piece or pieces of data that uniquely identify one, and only one, record in the domain. Manually incremented data is not a key. It's a sequence number.

99.999% of all correctly designed tables will have a natural key. It may be one or more fields in a composite, but it will be there.

But autonumbering has nothing to do with referential integrity, per se. You shouldn't use it anyway.

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[jimmythegeek]

Jim,

Fancy meeting you here on this subject. I think by voicing that autonumbers should not be used, you are going to have a lot of beginning programmer's confused. You are now going to have individuals wondering how to create data models if they have not been taught your way.

I would argue that autonumbers DO create referential integrity (you know the argument), and it is the best way for beginners to learn how to create data models.

I think to come out and say "you shouldn't use them", is a matter of opinion, and the comment "99.999% of all correctly designed tables will have a natural key", assumes that a beginner knows how to create a correctly designed table.

If a person decides to create a database with autonumbers, this does not automatically disqualify them from having a valid model.
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jerseyboy]

Thanks Gentlemen for weighing in on the subject,
Is there a possiblity of you both collaborating on a FAQ/TIP on the subject? I am especially interested in how to create the correct table structure, and how to implement composite keys. Although I know what a composite key is, I was warned away from it way back when! As stated above, I too have used autonumbers on ocassion, but only as a last resort (mainly due to time constraints). I have read the FAQ on creating a Primary Key (MichaelRed) and wonder if that qualifies for your discussion?

I am very eager to see what results.
Thanks I enjoy reading your responses wherever I go, JerseyBoy
Remember: self-praise is no recommendation

 

[930driver]

I'll disagree with Jim. There is no reason not to use autonumbers for a key, provided you never use it as a substitute for a natural key! which every table should have.

By all means use it for relationships and joins, but never allow it to be seen by the user nor hold any other meaning.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[jimmythegeek]

I agree with that, I never said you should use an autonumber IN PLACE of a natural key. No argument here. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[LittleSmudge]

This discussion brings us into the classic 'education/development problem of:-
Do you, as Jim H promotes "Teach students the RIGHT way, from the start - even if it is hard and they may find it more of an uphill struggle - but will do it naturally the right way for ever after"

Or as Jim L suggests "Get them going in whatever way seems to work for them and then go back and promote good practice after they've had a chance to get comfortable with bad habits."

( I know that it polarising your views a bit beyond reality - but I hope it clarifies the discussion a bit for others and helps people realise that you to Jims are not disagreeing on the fundimentals of Relational Database Theory - just on the development of designers. )

[ For the record, as a trainer of many year's experience ( in HR as well as IT ) I'm firmly in the teach them early and teach them right camp. ]



G LS

 

[930driver]

I guess I should have quailified which Jim I was disagreeing with. That'd be WildHare who, if I understand him, sees no use for an autonumber what-so-ever. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[WildHare]

Are we havin' fun yet??

You know, despite all the tossing back and forth in these threads, I tend to think they're a good idea, because not only does it force us grizzled old veterans to shake the grey matter loose a little bit every now and then, but newer "less travelled" users can hopefully read them and get some ideas themselves. And I hope we're all taking these posts in good humor, I know I do. Sometimes my posts might come off a little "gruff" or whatever, that's not the intent.

I'd be more than happy, as JerseyBoy has suggested, to collaborate with others here and come up with some sort of FAQ or "Almost everything you ever wanted to know about relational design but were afraid to ask" sort of thing, as long as we don't fall into the trap of assuming we're the last and final word on things and pontificate here like some sort of Roman pantheon. We can all only draw on our own experiences, I suppose.

930, you make an excellent point about not ever letting a user SEE the autonumber. If they don't know it's there, they don't have to worry about it.

So what say, guys and gals, shall we put some ideas down on virtual paper and come up with some useful FAQ material? My website had my email address, please feel free to communicate.

Maybe we can even solicit ideas from people like Celko and Getz. Ken responded to an email of mine a year or so back about date fields, so I know they occasionally do answer us peons.



78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[jimmythegeek]

I hope we are having fun.

LittleSmudge,

I would never suggest teaching someone how to "incorrectly" create a data model. You see, I don't think the use of autonumbers in the right situation is "wrong".

If there is no natural key, and you have the choice of using a number field and incrementing it yourself, or an autonumber (assuming the number field has no particular "meaning&quot, why is using an autonumber wrong?

For instance, at my company, they are no longer using SSN as the employee indentifier because of security reasons (I am for that). So they gave us an Employee ID. It is a 6 digit number that has no significant meaning to me whatsoever. It is a number I use all the time. Now what difference does it make if the number is an autonumber or number field? It is simply a unique number that identifies me as different from the other employees (over 20,000). As an employee, I don't care how they got the number, all I know is I need to memorize it, and until I leave, that is my ID.

I understand the point about not being able to start the beginning autonumber value where you want, however, check out Laois' response to that in this thread thread702-245367. This is code I had not seen before.

Excellent dialog.

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?