Spidey

Primary Key as Text and/or Number

4 posts in this topic

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

Share this post


Link to post
Share on other sites

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.

1 person likes this

Share this post


Link to post
Share on other sites

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.

1 person likes this

Share this post


Link to post
Share on other sites

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 :thumbsup:) 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

1 person likes this

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now