OraFAQ Forum: SQL & PL/SQL » Using CHECK constraint to validate two types of telephone formatted insert data

Home » SQL & PL/SQL » SQL & PL/SQL » Using CHECK constraint to validate two types of telephone formatted insert data (Oracle 11g Enterprise Ed. Release 11.2.0.1.0 - 64bit, Win 10 Home 64 bit Ed)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using CHECK constraint to validate two types of telephone formatted insert data [message #662723]

Wed, 10 May 2017 04:56

spd800c
Messages: 2
Registered: May 2017

Junior Member

I have following membership table

Name                Null?    Type              
------------------- -------- -------------
MEMBERSHIP_ID       NOT NULL NUMBER(6)     (PK)
MEMBERSHIP_DATE     NOT NULL DATE              
FNAME               NOT NULL VARCHAR2(30)      
LNAME               NOT NULL VARCHAR2(30)      
DYTIME_PHONE        NOT NULL VARCHAR2(13)      
EVTIME_PHONE        NOT NULL VARCHAR2(13)      
GENDER              NOT NULL CHAR(1)           
DOB                 NOT NULL DATE              
ADDRESS_LINE        NOT NULL VARCHAR2(100)     
CITY                NOT NULL VARCHAR2(40)      
COUNTY              NOT NULL VARCHAR2(50)      
POST_CODE           NOT NULL VARCHAR2(8)       
EMAIL               NOT NULL VARCHAR2(50)

I need to validate the 'DYTIME_PHONE' and 'EVTIME_PHONE' data that is inserted into the above table. The data entered into these columns can be of the following -

01932-424-122 or 0207-435-4006 or 077576238333

I have chosen to do the following at database level (below eg, for DYTIME_PHONE) -

alter table membership
ADD CONSTRAINT DYTIME_PH_LENGTH_CK CHECK (REGEXP_LIKE(EVTIME_PHONE,'[0-9]{4}-[0-9]{3}-[0-9]{3}+\|[0-9]{4}-[0-9]{3}-[0-9]{4}+\[0-9]{11}'))
/

My question is - I feel this is not the correct way to enforce the required validation?

[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Wed, 10 May 2017 05:07] by Moderator

Report message to a moderator

Re: Using CHECK constraint to validate two types of telephone formatted insert data [message #662724 is a reply to message #662723]

Wed, 10 May 2017 05:59

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why not use OR between the different correct regular expressions instead of a single doubtful one?
This will be easier to read and maintain.

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Using CHECK constraint to validate two types of telephone formatted insert data [message #662725 is a reply to message #662723]

Wed, 10 May 2017 06:13

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Your regexp is bad. Also, I'd use TRANSLATE - it would be faster.

ALTER TABLE MEMBERSHIP
  ADD CONSTRAINT DYTIME_PH_LENGTH_CK
    CHECK(
          TRANSLATE(EVTIME_PHONE,'123456789','000000000') IN (
                                                              '00000-000-000',
                                                              '0000-000-0000',
                                                              '000000000000'
                                                             )
         )
/

SY.

Report message to a moderator

Re: Using CHECK constraint to validate two types of telephone formatted insert data [message #662726 is a reply to message #662725]

Wed, 10 May 2017 06:21

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You should also make the name match the column you're checking to avoid confusion.

Report message to a moderator

Re: Using CHECK constraint to validate two types of telephone formatted insert data [message #662727 is a reply to message #662726]

Wed, 10 May 2017 06:52

spd800c
Messages: 2
Registered: May 2017

Junior Member

Thanks Solomon for that. If it is faster than yes I'd rather go for that.

Michel do you mean Or as below?

alter table membership
ADD CONSTRAINT DYTIME_PH_LENGTH_CK CHECK (REGEXP_LIKE(DYTIME_PHONE,'.....-...-...|....-...-....|..........'))

Report message to a moderator

Previous Topic:	problem with FLOOR function
Next Topic:	SP2-0552: Bind variable "LIMIT" not declared.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 06:40:45 CDT 2024