Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Unanswered: How to create CHECK constraint on table

    I need to add a "check constraint" on a varchar2 column.
    The constraint is:
    the value in the column must be either numbers or characters a-z or A-Z. It should not have any special chars such as ~!@#$%^& etc......
    What could be the syntax ?

    for example for a "Check of T or F " on column "name" will be like this.
    create table test1 (
    ID VARCHAR2(10),
    NAME CHAR(1) CONSTRAINT Con1
    CHECK (UPPER (NAME) IN ('T', 'F')),
    DESC VARCHAR2(30)
    );

    similarly I want to check the DESC column is either 0-9 or a-z or A-Z or combination of numbers and characters.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    1
    Hai,
    Would it be fine if you use 'not like' clause with 'AND' option for
    all the values that you want to exclude.

    eg

    create table c
    (name varchar2(30) constraint chk_name
    CHECK (NAME not like '%~%'
    and NAME not like '%#%'
    and name not like '%^%'))


    Table created.

    Result :

    SQL> insert into c values ('A~B#');
    insert into c values ('A~B#')
    *
    ERROR at line 1:
    ORA-02290: check constraint (SCOTT.CHK_NAME) violated


    SQL> insert into c values ('A^B');
    insert into c values ('A^B')
    *
    ERROR at line 1:
    ORA-02290: check constraint (SCOTT.CHK_NAME) violated

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to create CHECK constraint on table

    This is one way:

    CHECK( TRANSLATE(UPPER(desc),'#0123456789ABCDEFGHIJKLMNOP QRSTUVWXYZ','#') IS NULL )

    i.e. when you remove all the letters and numbers, there should be nothing else left.

Posting Permissions

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