...
- As a primary key field, used for maintaining referential integrity. The primary key field identifies a single record within table, now and into the future. Your users should never be able to see or change this number. When storing your data in a SQL database, primary keys are critically important, as Clarion’s POSITION function depends on them.
- As a meaningful number (e.g. invoice number). These are visible to the user, and represent the human identifiable items. The user could theoretically decide to change the way these are assigned and formatted, so you don’t want to use these as your primary key.
How Does Clarion Do It?
In the very early days of Clarion, auto-numbering had to be done manually. It wasn’t too long, though, before Clarion gave us a very handy method to achieve auto-numbering directly in the options for the key. As long as the key was marked as unique, it could also be auto-numbered. Not only that, it automatically handled multi-component keys, so you could auto-number detail rows associated an invoice header.
...
- For those who want human readable numbers, there are other ways around that (see “sequences” below).
- For header+child scenarios, the best solution is to handle it yourself with a “shell game”. Make use of a temporary store for the detail items, during data entry. The easiest approach is to use an in-memory database (a.k.a. IMDD) or a separate table in your real database. It’s easy to change the existing browses and forms to point to this different table. Then you write a small bit of code to shuttle the detail records between this temporary store and your regular database table. You assign the row number yourself when you add children. The consequent benefit is that partial edits don’t muck up your database, requiring that you clean them up when a user cancels.
If you desperately don’t want to change your code, then there’s an EMULATEAUTONUMKEY option. It tells Clarion to use SQL for the auto-numbering, but still creates a placeholder when you start a form in Insert mode. I feel this is a bad design decision, so I’m not going to cover it in any depth. If you want to risk its use, then it’s covered in the help.
Auto-Numbering Alternative: UUID
It’s worth mentioning that for primary keys, you don’t strictly need an auto-numbered field. It must merely be unique. You can use a “UUID” (Universally Unique ID), also known as a GUID (Globally Unique ID). The terms are often interchanged, but strictly speaking a GUID is considered to be a Microsoft variant of a UUID. You could use a Microsoft GUID for your primary key field, or simply use a STRING field with random characters and numbers (i.e. Base 36). Ensure that the field is long enough for the value to be unique (e.g. 24 characters is roughly equivalent to a standard GUID), and assign the random value when the field is primed.
...
- SQL engines often default to using the primary key as the “Clustered Index”, which means it physically stores the records in that order. If you’re assigning random values, then the server will be reworking the file regularly to get those records in order. Therefore, you must explicitly tell your database to use a different key as the clustered key.
- By the way, I've read that choosing the best key for clustering is one of the best techniques for efficient queries, and supposedly the primary key isn't usually the best key, so this is something you should ponder regardless.
- According to Bruce Johnson at Capesoft, the speed impact is small, but I’ve spoken with administrators working on large databases with many new transactions each day, and they were uneasy about using this approach.
- Long strings take up more space than compact integers, so your database will be bigger (and consequently slightly slower).
- When debugging, working with random strings isn’t as easy as sequential numbers.
Do I Really Have to Change?
The short answer is “No.”
...
In the end, though, it’s your decision. If you’re still interested in learning more, then please read-on.
On with the Show!
Now that you understand the pros and cons of Clarion’s auto-numbering, let’s look at how SQL fits into the picture. Unfortunately, it’s not as easy as flicking a switch in the key definition in your dictionary. Clarion’s database drivers do a great job of making most database access generic, but auto-numbering is done differently with each engine (and sometimes different versions of SQL flavors change how they do it with new versions). That’s why you must approach auto-numbering as a “custom” task.
...