Primary Key as Text and/or Number

[Spidey]

If I have a field for my product number, for example, P001(text), and I like to set it as my primary key.  Is it necessary to create another primary id (number) for internal use?

KC

[comment]

No, it is not "necessary" to have another field, as long as your product number is both unique and permanent.

There are some advantages to having a numeric matchfield (e.g. the index is smaller), but you have to balance that against adding another field to your schema. And your existing field is likely going to get indexed too anyway.

[Agnes Riley]

You generally want to keep your ID (primary key) to a number. If for nothing else, because if you want to use ExecuteSQL or ESS it will just bite you in the butt if you have a text ID.

[Mark Scott]

Hey KC,

In addition to the considerations mentioned by comment and Agnes, there is one more thing you might consider if your table is (or is likely to grow to be) large.  JBante (who, I see, liked both of the above responses ) did some testing and found quite a difference in performance in record creation, finds, etc., between text and strictly numeric keys.  Probably relates to comment's observation re index size.  In many cases the difference will be academic, with little real-world impact, but it may make a palpable difference in some settings.  I've tended to use Jeremy's numeric UUID function, but that's just my preference, and I've always been a fan of surrogate (meaningless) keys anyway.  (BTW, if you need a multi-valued key, as in Selector-Connector for example, that can be a text field and still link to numeric pk fields.)

hth,

Mark