Jump to content

Primary Key as Text and/or Number


Spidey

This topic is 3146 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
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.

  • Like 1
Link to comment
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

  • Like 1
Link to comment
Share on other sites

This topic is 3146 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.