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

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: Sun, 07 Jun 1998 17:04:38 GMT
Message-ID: <6leh75$h5e@bgtnsc02.worldnet.att.net>

On Fri, 05 Jun 98 14:00:45 GMT, Steven Moyano <smoyano_at_nwu.edu> (Steven Moyano) 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.
>
>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 /

The above syntax isn't quite correct. The following should work:

	create table sproj_tresearchers (
		researcher_ssn char(9) not null,
		constraint ckc_researcher_ssn check 
			(researcher_ssn between '0' and '9')
		);

Note that there is a trailing comma after the column definition. Also note that the entire CHECK condition is within parenthesis, and that the column name must be included in that.

All that having been said, I'm not sure your approach will work. Social Security number is nine characters long. Consider that a value of '1notanum' will be between '0' and '9'. Take a look at the constraint in the table below. It will force the column to have exactly nine numeric digits.

create table sproj_tresearchers (

		researcher_ssn char(9) not null,
		constraint ckc_researcher_ssn check 

(translate(researcher_ssn,'0123456789X','XXXXXXXXXXZ')='XXXXXXXXX')
		);

Here's the output from when I tested this:

SQL> create table sproj_tresearchers (
  2 researcher_ssn char(9) not null,   3 constraint ckc_researcher_ssn check   4
(translate(researcher_ssn,'0123456789X','XXXXXXXXXXZ')='XXXXXXXXX')   5 );

Table created.

SQL> insert into sproj_tresearchers values   2 ('123456789');

1 row created.

SQL> insert into sproj_tresearchers values ('12345'); insert into sproj_tresearchers values ('12345')

                                              *

ERROR at line 1:
ORA-02290: check constraint (SQLPLUS.CKC_RESEARCHER_SSN) violated

SQL> insert into sproj_tresearchers values ('123X56789'); insert into sproj_tresearchers values ('123X56789')

                                                  *

ERROR at line 1:
ORA-02290: check constraint (SQLPLUS.CKC_RESEARCHER_SSN) violated

SQL> regards,

Jonathan Gennick Received on Sun Jun 07 1998 - 12:04:38 CDT