Re: Using BETWEEN '0' AND '9' in table constraint

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 05 Jun 1998 16:15:24 GMT
Message-ID: <357c18de.10214267@192.86.155.100>

A copy of this was sent to Steven Moyano <smoyano_at_nwu.edu> (Steven Moyano) (if that email address didn't require changing) On Fri, 05 Jun 98 14:00:45 GMT, you wrote:

>
>I am trying to limit data entry into a CHAR 9 social security number column to
>integers. We have not used a NUMBER field since users search on partial social
>security numbers.
>

You can still use a number.

SQL> create table demo
  2 ( ssn number );

Table created.

SQL>
SQL> insert into demo values( 123456789 );

1 row created.

SQL> select * from demo where ssn like '%5%';

       SSN

---

 123456789

Oracle will automatically convert it for you...

>Here is a test table and the error message:
>
>imgntsvr1dosp> CREATE TABLE SPROJ_TRESEARCHERS
>2 (
>3 RESEARCHER_SSN CHAR(9) NOT NULL
>4 CONSTRAINT CKC_RESEARCHER_SSN CHECK (BETWEEN '0' AND '9')
>5 )
>..
> 15 /
> CONSTRAINT CKC_RESEARCHER_SSN CHECK (BETWEEN '0' AND '9')
> *
>ERROR at line 4:
>ORA-00936: missing expression
>

The correct syntax for that would be:

SQL> create table demo
  2 (

  3      ssn        char(9) not null
  4                      constraint ssn_is_number
  5                      check ( ssn between '0' and '9' )

  6 )
  7 /

Table created.

but it doesn't do your check (to make sure the ssn is a number). For example:

SQL> insert into demo values ( '123456789' ); 1 row created.

SQL> insert into demo values ( '123x56789' ); 1 row created.

it let both the good and bad SSN's in...

>Thanks very much for your time and help.
>
>
>Steven Moyano
>Northwestern University, Evanston, IL. USA
>Steven Moyano <smoyano_at_NWU.EDU>

If you want to stick with a char(9), then it could look like:

SQL> create table demo
  2 (

  3      ssn        char(9) not null
  4                      constraint ssn_is_number
  5                      check ( translate( replace(ssn,'x','y'),
  6                                        '0123456789',
  7                                        'xxxxxxxxxx' ) = 'xxxxxxxxx' )

  8 )
  9 /

Table created.

SQL>
SQL> insert into demo values ( '123456789' );

1 row created.

SQL> insert into demo values ( '123x56789' ); insert into demo values ( '123x56789' )

            *
ERROR at line 1:
ORA-02290: check constraint (TKYTE.SSN_IS_NUMBER) violated  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jun 05 1998 - 11:15:24 CDT