Primary Key

[RexesOperator]

Can a PK be a calculated field?

I want to be able create a PK that consists of three strings being added together. The first two would be collected from cascading combo boxes. The third field is a simple text field.

I would then run DLookup to make sure the final string is unique.

I have reasons for not using an autonumber field (there are compatibility issues with existing tables from a much older dbase database).

 

[pbaldy]

Rather than add a calculated 4th field, you could simply make the other 3 fields keys (called a compound key).

 

[RexesOperator]

The problem is the last text field is need as part of the ID. So is this doable? I realize it probably violates a number of rules, but this is a unique situation.

 

[pbaldy]

Yes, it can be done.

 

[The_Doc_Man]

First, use DCOUNT (not DLOOKUP) to assure uniqueness. Using Asimov's theory of numbers, there are only three numbers of interest: 0, 1, and many. If you have DCOUNT returning 0, your index could be unique. If 1, it IS unique (and in use). If anything else (many), then it is not unique.

Second, a PK can be a concatenated field or a compound key but in the strictest sense of your question, it cannot be a computed field - for the simple reason that you cannot put a formula in a table. Queries can have formulas but don't happen to have keys of their own. (They inherit keys from the component tables.) But that's a techie quibble.

Now, how you derive the intended value of a key is your business. Having a mixture of drop-downs and direct-entry text is as legal as any other method as long as you check what you did before you try to store it. Or trap the error you would get if you tried without checking.

If the resultant key is truly a candidate key (i.e. meets uniqueness tests and relevance tests), then no, this doesn't violate normalization.

 

[Pat Hartman]

I have been turned to the dark side and now use only autonumbers as primary keys. They are just so much easier to work with especially if you find you need to use them for combo or list boxes (these only work with a SINGLE unique field so multi-field PKs won't work for them) or if you need to create junction tables. When I have a constraint such as this that is left over from a legacy application, I make it a unique index so that I can enforce the business rule that the value must be unique but I can still use autonumbers for all joins within "my" application.

 

[RoyVidar]

It's ages since I did something like that, but I don't think it's impossible to use "multi-field PKs" with combos or lists, neither very difficult. Isn't it just setting the bound column to 0, then do some smallish code in the forms on current event and the combo/list after update event?

 

[Pat Hartman]

Perhaps you'd like to try and then report back to us. The issue is that you can only have a single bound field and that must be unique or you'll get some strange results. You can get around the multi-field PK issue by creating a query that concatenates the keys into a text string but you'll still need code to actually save the real PK rather than the string. An autonumber in this case is far easier to use.

 

[RoyVidar]

I have tried, several times (though it is a while ago), and I did consider my previous reply as the report you're asking for. I think I gave all relevant information there, but let me repeat them, and try to be a bit more precise.

If you don't have a single unique field in the rowsource, then there is inbuilt functionality to create such, which I described in my previous reply. Setting the Bound Column property of the combo to 0, makes the Value property return the ListIndex - which is unique (there is no need to do any concatenation scheme). Check it out in the help file.

When suggesting to set the Bound Column to 0, I'm not suggesting to store the ListIndex, I should perhaps have been clearer on that, but the combo or list, need to be unbound, and syncing it with the form recordsource, will need to be performed, for instance in the forms on current event.

In addition, one would also need to assign values to the foreign key field programatically, in the after update event of the combo or list.

This is what i refer to as "some smallish code".

So, unless you're getting those strange results in the attached, then my view is that this is not impossible (as you suggested), it is in my view not even difficult. Though it is quite simple to use combos and listboxes with one field pks, I don't really see this here as more difficult. It's just "some smallish coding".

 

[Pat Hartman]

That was an excellent example. Thank you for sharing. But my position remains - use an autonumber as the PK whenever you have no single unique candidate key (or all the time for consistancy). It will prevent you from having to create code such as the example posted by RoyVidar to get around the issue of combo and listboxes only supporting a single bound field.

The flaky results occur when you bind the combo to a non-unique field. Not when you make it unbound as you have in your example.

 

[RoyVidar]

Thank you!

 

[The_Doc_Man]

Here, I am simultaneously a pragmatist and a purist.

I'm with Pat in that when you have no single candidate key, you will have easier work ahead by using an autonumber PK.

But... I'm also with those whose business naturally generates something that would be a legit candidate key as PK. Adding an autonumber PK is, at that point, clutter.

Where is the dividing line? Size of the key. An autonumber is a LONG integer. If your compound PK is longer than, say, two LONGs, you are already starting to consume index space like it was going out of style. You'd do better with shorter keys. It is a geometric type of progression as to how much worse it gets as you add bytes to the size of the PK.