Question regarding Primary/Secondary Keys !! 1

[Lostdude]

Hi guys,
I am trying to learn Access and i have a quick question regarding the primary keys. I know that the primary key is supposed to be a unique field and not null, but can it contain Alphabets or a combination of alphabets and numbers? Whats is the best practice regarding choosing the field for the key ?
Thanks tons ..

 

[fneily]

This is an extremely basic question. My first suggestion is that you buy any Access book and read, study and try that before jumping into the Access forums. There is some Access knowledge that is assumed when questions are answered.
Primary keys can be of any creation you want as long as it makes the record unique. It also helps if the user can tell what the Primary key means. eg, Smi1 - first three letters of a person's last name and a number appended to avoid duplicates. The user could then guess that they'll dealing with some sort of employee or personnel record.

 

[Remou]

It is not quite a quick question, you may wish to read:

Natural or Surrogate Key:

http://r937.com/20020620.html

and

http://www.r937.com/relational.html

 

[jimirvine]

@fneily, what if the staff member's surname changes? Do you then change the value of the PK? That would break the rule of a PK being 'immutable over time'
As remou implies, this is NOT an 'extremely basic question' it is however, an extremely common question.

Here is a nother link to one of many, many discussions.

http://www.utteraccess.com/forums/s...r=641812&page=0&view=collapsed&sb=5&o=21&vc=1

 

[Lostdude]

Thanx guys for all the replies.. Appreciate it..

 

[fneily]

jimirvine - I thought eg. meant "for example"? Oh yeah, it does.
Must be a basic question. After you decide on the fields in a table and after normalization, a primary key should be created. Thus a basic routine. If you don't create primary keys, then how would Access be effective? Again, a basic question.

 

[jimirvine]

jimirvine - I thought eg. meant "for example"? Oh yeah, it does.

What's your point? I genuinely don't understand what you are trying to say here. You are trying to say that you were just giving an example? If that is the case, then you were giving a bad example.
I still disagree that this is a basic question. A common question yes. Basic, no. But you are entitled to your opinion, I would have thought, however, that if it was such a basic question, then you could have come up with a better and more valid/acceptable example.

 

[r937]

whether or not a table has a primary key is, in my opinion, not a basic question

neither msaccess nor any other popular relational database requires that the table must have a primary key, yet the lack of one does not prevent the table from being effective

as for immutability, it is a good idea to choose a primary key that changes infrequently, but it is not an absolute requirement

after all, changes in primary keys is what ON UPDATE CASCADE takes care of

ON UPDATE CASCADE is an option on foreign keys that in msaccess is managed via the Relationships Editor

when you link two tables, it presents you with a widget such as this:

r937.com | rudy.ca