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.
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.
Originally Posted by r937
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?