By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

check constraint

P: 1
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
Jul 30 '07 #1
Share this Question
Share on Google+
2 Replies


P: 5
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
  1. alter table sample add
  2. check(substr(char(decimal(substr(col1,1,15),15,0)),1,15) = substr(col1,1,15) and col1 like '%    ')
  3.  
Good luck.
Jul 31 '07 #2

P: 57
The following alternative avoids the SQL conversion error:

Expand|Select|Wrap|Line Numbers
  1. create table sample (col2 TIMESTAMP NOT NULL,
  2.                      col1 CHAR(19) NOT NULL,
  3.                      col3 CHAR(1) NOT NULL,
  4.                      primary key(col2,col1,col3));
  5.  
  6. alter table sample add
  7.       check(translate(substr(col1,1,15),'X          ',' 0123456789') = ''
  8.         and substr(col1,16,4) = '');
  9.  
  10. insert into sample values(current timestamp, '123456 89012345    ', 'X');
  11. insert into sample values(current timestamp, '123456B89012345    ', 'X');
  12. insert into sample values(current timestamp, '1234567890123456   ', 'X');
  13. insert into sample values(current timestamp, '123456789012345    ', 'Y');
Aug 2 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.