Issue with Primary key being more than one field?

[DNS809904]

Hello,
I have a basic question about choosing the primary key for a table. Now I am not really concerned on the particular field(s) chosen to be the primary key, but rather the potential states, if you will, that the key can be in.

Say I choose a multiple field primary key for a contact table of, say, LastName, FirstName, and PhoneNum.

I thought during an Access db project I was updating awhile ago using Access 97, it didn't require EVERY field of the multiple primary key to be filled in, as long as there was enough to make a unique key.

I am wondering if this is still the case for Access2000 primarily. From what I have read it appears that this is not the case or at least it is strongly not recommended. For my example would it be to have FirstName and LastName filled in but not PhoneNum. Then, if a new Contact were added with the same LastName and FirstName but a PhoneNum was also added, the PrimaryKey would essentially be unique still.

I know this definitely isn't the best example but it still states my point.

If anybody has any wisdom on the subject I'd greatly appreciate it!

Thanks!
Dana S.

 

[RoyVidar]

I would have thought that the complete composite primary key needed to be not null, that's how xp behaves, anyway.

Would you by any chance have misunderstood, could it have been a compound index and not a compound primary index/key? A compound index (which is not the primary key) can allow null in one or more fields.

 

[DNS809904]

Would you by any chance have misunderstood, could it have been a compound index and not a compound primary index/key? A compound index (which is not the primary key) can allow null in one or more fields.

Thanks for the reply Roy. It is possible that what I thought was a composite primary key was just a compound index. It has been awhile since I worked with that database.... I'll have to dig it up and check for sure. It was done in Access 97 so I still think it's possible for that version to have behaved that way, being it a right or wrong practice to control primary keys.
However it does now appear obvious you can't do that to composite primary keys with newer versions of Access (2000 and newer).

Live and learn!
Dana

 

[RoyVidar]

Can't make that work on my 97 either

 

[DNS809904]

Can't make that work on my 97 either

Well alrighty then.. I'll take your word for it but now I am DEFINITELY going to look back and see what the situation was with my old database.
Thanks again!
Dana

 

[neileg]

Whether this works or not, your primary key should be one that you can guarantee will be unique in all reasonable situations. If John Brown (Snr) and John Brown (Jnr) share the same phone, you'll get a duplicate key value. Think carefully about this before you commit to the design.

 

[DNS809904]

Whether this works or not, your primary key should be one that you can guarantee will be unique in all reasonable situations. If John Brown (Snr) and John Brown (Jnr) share the same phone, you'll get a duplicate key value. Think carefully about this before you commit to the design.

Thanks for the concern of my crappy design Neil. However that was just an imaginary example... I was only thinking about the functionality of the primary key, and not the chosen fields for the primary key.
Still, it's good to know people are looking out for one another!
Thanks again,
Dana