Create table with check(column like 'ed10[0-9]')

Page 1 of 1

2 Replies - 2201 Views - Last Post: 03 September 2012 - 10:54 AM

#1 rnty  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 131
  • Joined: 14-August 12

Create table with check(column like 'ed10[0-9]')

Posted 14 August 2012 - 09:42 PM

Hello Respected members,

This is my first post in dream.in.code.

I am using Oracle 10g.

SQL> create table table1(no varchar2(10),name varchar2(10),check(no like 'ed10[0
-9]'));

Table created.

SQL> insert into table1 values('ed101','r');
insert into table1 values('ed101','r')
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C004024) violated

I have no clue what is wrong here.

Thank you in advance

Is This A Good Question/Topic? 0
  • +

Replies To: Create table with check(column like 'ed10[0-9]')

#2 fuzz_collective  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 02-August 12

Re: Create table with check(column like 'ed10[0-9]')

Posted 02 September 2012 - 10:19 AM

The constraint is checking for [0-9] as a string literal, how about something like this instead?

CREATE TABLE table1( 
no varchar2(10), 
name varchar2(10), 
check(NO LIKE 'ed10_'),
CHECK( to_number(SUBSTR(NO,5,1)) <=9 
);

Was This Post Helpful? 1
  • +
  • -

#3 rnty  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 131
  • Joined: 14-August 12

Re: Create table with check(column like 'ed10[0-9]')

Posted 03 September 2012 - 10:54 AM

Yes that works.I had come to the conclusion that it is not possible to set such a constraint in oracle
Thank You
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1