Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: pet table constraint problem :'(

    hey i've created a table and I attempted to put the contraint that
    the the pet tag has to be 6 character long, with the first 3 characters are letters from A-Z, the fourth character MUST be either 1,3 or 5 and the last two characters are between 0-9. I don't think the constraint i've written is doing what i'm intending to do because when i try to INSERT a row into the table
    i violated that contraint. Could someone please tell me how to fix my constraint so it works as i've specified.


    Code:
    CREATE TABLE PET
    (
    Pet_Tag char(6) NOT NULL,
    Name char(50) NOT NULL,
    PRIMARY KEY(Pet_Tag), 
    CONSTRAINT PET_ID_Format CHECK(Pet_Tag LIKE 
    '[A-Z][A-Z][A-Z][1,3,5][0-9][0-9]')
    );

    Code:
    INSERT INTO PET VALUES('abc166','Tom');
    ERROR at line 1:
    ORA-02290: check constraint (torus.PET_ID_FORMAT) violated

    Thanx

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    I just wonder, where you found Oracle LIKE condition. It is described in the documentation, but hey ... you shall already know where to find it. You would find there, that it does not support regular expression, it checks the exact match of the string (it supports some wild cards, but none of them is used in given pattern).

    From 10g above, REGEXP_LIKE for treating regular expressions was introduced. Your regular expression looks good except for commas between numbers ([135] is correct). Where did you find this regular expression syntax? I would also add meta characters for start (^) and end ($) in the format mask, but it would not be necessary as the string has fixed number of characters.

  3. #3
    Join Date
    Sep 2009
    Posts
    16

    Ok...so is this right then?

    hey, i don't have any oracle or sql plus prog. on my computer so I couldn't really test your suggestions. So would the following two be correct then? I jst had a readup on REGEXP_LIKE and want to verify that i've used it correctly as I can't test it atm.

    Code:
    CREATE TABLE PET
    (
    Pet_Tag char(6) NOT NULL,
    Name char(50) NOT NULL,
    PRIMARY KEY(Pet_Tag), 
    CONSTRAINT PET_ID_Format CHECK(Pet_Tag REGEXP_LIKE
    '[A-Z][A-Z][A-Z][135][0-9][0-9]')
    );

    Code:
    CREATE TABLE PET
    (
    Pet_Tag char(6) NOT NULL,
    Name char(50) NOT NULL,
    PRIMARY KEY(Pet_Tag), 
    CONSTRAINT PET_ID_Format CHECK(Pet_Tag REGEXP_LIKE
    '^[A-Z]{3}[135][0-9]{2}$')
    );
    Thanx

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by Castiel
    hey, i don't have any oracle or sql plus prog. on my computer so I couldn't really test your suggestions.
    Since Oracle 10g Express Edition is completely free and easy to install, your excuse is poor.

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Well you are specifying caps and trying to insert lower case, so its still not going to work.

Posting Permissions

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