Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    Unanswered: n00b questions on basic DDL

    Do I have it right that MySQL does not enforce check constraints, such that the following would not work?
    Code:
    CREATE TABLE stands
    (
       standID   INTEGER   NOT NULL   PRIMARY KEY 
            CHECK (standID BETWEEN 100000000 AND 999999999)
    ,  slope      INTEGER   NOT NULL  
    );
    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"?
    Last edited by rbfree; 03-18-09 at 21:00.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    104
    Why wouldn't MySQL support CHECK Constraints. Are they somehow deprecated or otherwise problematic? It seems to me that they'd be pretty handy.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    104
    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.
    Last edited by rbfree; 03-19-09 at 00:14.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a couple of thoughts
    Code:
    CREATE TABLE meh (
       blah INTEGER NOT NULL PRIMARY KEY
    )
    auto_increment = 100000000;
    
    ...OR...
    
    SELECT Right('000000000' + Convert(varchar, your_pk), 9) As nine_chars_long;
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2009
    Posts
    104

    key values are from somewhere else

    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.


    Quote 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?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •