Oracle check regexp if a condition is satisfied

Question

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

From Technet, `The Oracle REGEXP_LIKE condition allows you to perform regular expression matching in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.`http://www.techonthenet.com/oracle/regexp_like.php — Vamsi Prabhala, Nov 17 '15 at 22:03
? http://stackoverflow.com/questions/1167767/check-constraint-of-string-to-contain-only-digits-oracle-sql or perhaps `LENGTH(TRIM(TRANSLATE(phone, ' +-.0123456789',' '))) is null` — xQbert, Nov 17 '15 at 22:10
Similar question about check constraint containing case: http://stackoverflow.com/questions/8072463/oracle-sql-can-case-be-used-in-a-check-constraint-to-determine-data-attributes — Doug Porter, Nov 17 '15 at 22:23

score 1 · Answer 1 · answered Nov 17 '15 at 22:15

1

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));

answered Nov 17 '15 at 22:15

Martina

929
7
19

If I write that constraint, and I insert 112233445A, says that comma is missing. Why? – v8rs Nov 17 '15 at 22:34
1

could you please show your exact error message? it works for me on 12c, SQL> insert into citizen values ('Finland', '112233445A'); insert into citizen values ('Finland', '112233445A') * ERROR at line 1: ORA-02290: check constraint (C##MINA.CHECKPHONE) violated – Martina Nov 17 '15 at 23:01

score 0 · Answer 2 · answered Nov 17 '15 at 22:19

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.

@v8rs please post your updated constraint definition and the error you get. I do not have access to a 10g instance but I don't see any features related to this that were added in 11. — Doug Porter, Nov 18 '15 at 16:07

Oracle check regexp if a condition is satisfied

2 Answers2