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