Do I have it right that MySQL does not enforce check constraints, such that the following would not work?
If not, how could I accomplish this task. (If I'm forced to deal with "triggers," is there a preferred path?)

BTW, the check constraint is worded as found in one of many refs. Is it correct? Is there a way to write it without the words "between," and "and"?

yes, that's right, CHECK constraints are not yet supported in mysql

you can write a trigger if you really need this functionality (disclaimer: i've never written such a trigger myself)

CHECK (standID >= 100000000 AND standID <= 999999999)

Why wouldn't MySQL support CHECK Constraints. Are they somehow deprecated or otherwise problematic? It seems to me that they'd be pretty handy.

i dunno why

they're certainly not deprecated (which would refer to something that used to be supported but is no longer)

i expect CHECK constraints to be implemented in a future release real soon

R (or anyone), do you commonly use range-of-value constraints? Are they commonly used? If not, why not? (or if so, why?)

BTW, my objective for having a primary key between 100000000 and 999999999 (both 9 digits) is to make sure that in reports, all the keys are visually identical length-wise... Also, in a growth model that I use, the stand keys are 9 digits long. My solution (check constraint) might not be the most rational approach, in any case. It's what came to mind.

i've used range constraints before (it wasn't a mysql application, obviously)

your constraint for a range on PK values is unusual -- how were you planning to assign new values as you insert new rows?

common choices are (1) use an auto_increment, or (2) assign new values in the application logic

sounds like you already have some key values from somewhere?

Just a couple of thoughts

R,

You're correct. The key values come from another application -- a forestry application -- and identify forest stands. Many of the big land managers use 9 digit key for stands, with various tuples conveying different meaning (region, species, and stocking, usually) and it's possible that they actually create a composite key from another column. I know that if the key conveys multiple meanings, it is a multivalued field in that sense (and violates 1nf). But the number I see only functions as a primary key. Since I use it strictly as a p-key (and don't search for meaning by breaking into tuples and never will), it doesn't seem to pose an integrity problem.

I'll do a little research on the solutions you've offered. Thanks!

And George, thanks for this example. I need to go research auto_increment. I really appreciate all the tips.

that's a wonderful explanation

so that key is a natural key, as you are getting it from an outside source that you trust

auto_increment would not be appropriate

also, there's really no urgency, then, to ensure that the numbers aren't somehow "out of range" (which is what your CHECK constraint was for)