check constraint

vidhyat

Hi,

I need to create a table in db2 with 3 columns say col1,col2,col3 in db2.
For col1 [datatype is char(19)] i need to add a check constraint to check whether the first 15 characters are numbers & remaining 4 characters are blank spaces. i tried it in the following way,but its not working.

create table sample (

col2 TIMESTAMP NOT NULL,
col1 CHAR(19) NOT NULL CONSTRAINT Acc_Num_Frmt LIKE '[0-9]* ',
col3 CHAR(1) NOT NULL,
primary key(col2,col1,col3)

);

can any of u suggest me a solution ASAP

Thanks,
Vidhya

Chrisk

First of all, if the 15 characters must be digits, shouldn't you use a DECIMAL(15) data type instead?

However...

Add a CHECK condition to the table which converts the digits to a DECIMAL(15) and back to a CHAR(15). Then compare the resulting string to the original. You will get a conversion SQL error if they are not digits.

It is not trivial (I think):

Expand|Select|Wrap|Line Numbers

 
alter table sample add

check(substr(char(decimal(substr(col1,1,15),15,0)),1,15) = substr(col1,1,15) and col1 like '%    ')

Good luck.

cburnett

The following alternative avoids the SQL conversion error:

Expand|Select|Wrap|Line Numbers

 create table sample (col2 TIMESTAMP NOT NULL,

                     col1 CHAR(19) NOT NULL,

                     col3 CHAR(1) NOT NULL,

                     primary key(col2,col1,col3));
 
alter table sample add

      check(translate(substr(col1,1,15),'X          ',' 0123456789') = ''

        and substr(col1,16,4) = '');
 
insert into sample values(current timestamp, '123456 89012345    ', 'X');

insert into sample values(current timestamp, '123456B89012345    ', 'X');

insert into sample values(current timestamp, '1234567890123456   ', 'X');

insert into sample values(current timestamp, '123456789012345    ', 'Y');

check constraint

Post your reply

Similar topics