Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: Check constraint

    All,

    I am facing some problem whiile executing the below code.

    create table t1
    (
    eno char(2) constraint c1 Check ( eno like '%[A-Z][0-9]%')
    )

    insert into t1 values ('A2')

    is throwing error message.

    ORA 02290: CHECK CONSTRAINT VIOLATED.

    Where i am doing mistake?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    LIKE doesn't accept regular expressions. You would need to use REGEXP_LIKE. I've never used it, but I think the syntax would be:
    Code:
    regexp_like(eno, '[A-Z][0-9]')
    or equivalently:
    Code:
    regexp_like(eno, ':upper::digit:')
    Alternatively, you could use:
    Code:
    substr(eno,1,1) between 'A' and 'Z' 
    and substr(eno,2,1) between '0' and '9'

  3. #3
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    No. It is not working.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What isn't?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This works:
    Code:
    SQL> create table t1
      2  (
      3  eno char(2) constraint c1 Check ( substr(eno,1,1) between 'A' and 'Z' 
      4  and substr(eno,2,1) between '0' and '9' )
      5  );
    
    Table created.
    
    SQL> insert into t1 values ('A7');
    
    1 row created.
    
    SQL> insert into t1 values ('Z2');
    
    1 row created.
    
    SQL> insert into t1 values ('88');
    insert into t1 values ('88')
    *
    ERROR at line 1:
    ORA-02290: check constraint (TONY.C1) violated
    
    SQL> insert into t1 values ('AA');
    insert into t1 values ('AA')
    *
    ERROR at line 1:
    ORA-02290: check constraint (TONY.C1) violated

  6. #6
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    OK. Suppose if the length is 8 and all characters should be in UPPER case. do i have to use 8 substring functions?

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, if you need to check character by character. But maybe not. If all you want to check is that all characters are upper then:
    Code:
    check (eno = upper(eno))
    If you want a mixture of uppercase letters and numbers in some pattern like XX999999 (2 letters then 6 digits) then you could do:
    Code:
    check (translate (eno,
        'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
        'XXXXXXXXXXXXXXXXXXXXXXXXXX9999999999') = 'XX999999')
    In this case, a regular expression would be simpler if you can use REGEXP_LIKE.

  8. #8
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Thanks andrew!

    check (eno = upper(eno)) will fit for my requirement.

Posting Permissions

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