CODEWITHSUNDEEP
sqlregexoracle-databaseconstraints
v8rs
v8rs

Reputation: 307

Oracle check regexp if a condition is satisfied

I want to add a constraint to a table in my Oracle server.

How could I do to check a phone number is 10 numbers (not letters) if a user inserts a citizen from Finland?

This is my code:

ALTER TABLE CITIZEN ADD CONSTRAINT checkPhone
CHECK (CASE Country  WHEN  'Finland'  THEN REGEXP_LIKE(Phone,'^[0-9]{10}$') END );

But I get the following error:

ORA-00920: invalid relational operator

Upvotes: 1

Views: 766

Answers (2)

Doug Porter
Doug Porter

Reputation: 7897

A check constraint needs to resolve to a boolean expression and a case resolves to a value so you need to compare it to something. So if you are deadset on using case then it has to do a comparison. Or update it to not use a case like @mina wrote:

Set up our test data

create table citizen (
  id number,
  country varchar2(100),
  phone varchar2(10)
);

Table created.

insert into citizen values (10, 'Finland', '1234567890');
insert into citizen values (20, 'Ireland', 'abcdefghij');

Try to add our check constraint:

ALTER TABLE CITIZEN ADD CONSTRAINT checkPhone
  CHECK (CASE Country  WHEN  'Finland'  THEN REGEXP_LIKE(Phone,'^[0-9]{10}$') END );
                                                                                *
ERROR at line 2:
ORA-00920: invalid relational operator

But if we modify it to do a comparison:

ALTER TABLE CITIZEN ADD CONSTRAINT checkPhone
CHECK (case when country = 'Finland' and not REGEXP_LIKE(Phone,'^[0-9]{10}$') then 0 else 1 END);

Table altered.

Then we can test it:

insert into citizen values (10, 'Finland', '123456789z');

ERROR at line 1:
ORA-02290: check constraint (CHECKPHONE) violated

insert into citizen values (100, 'Finland', '1234567890');
1 row created.

Upvotes: 0

Martina
Martina

Reputation: 929

Try to rewrite condition in proper way

ALTER TABLE CITIZEN ADD CONSTRAINT checkPhone
CHECK (( Country  ='Finland' and REGEXP_LIKE(Phone,'^[0-9]{10}$')) or (Country!='Finland' or Country is null));

Upvotes: 1

Related Questions