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)
icon5.gif  Using CHECK constraint to validate two types of telephone formatted insert data [message #662723] Wed, 10 May 2017 04:56 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
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.

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2711
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.
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 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should also make the name match the column you're checking to avoid confusion.
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 Go to previous message
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,'.....-...-...|....-...-....|..........'))
Previous Topic: problem with FLOOR function
Next Topic: SP2-0552: Bind variable "LIMIT" not declared.
Goto Forum:
  


Current Time: Sun Feb 25 17:43:49 CST 2018

Total time taken to generate the page: 0.01229 seconds