CODEWITHSUNDEEP
oracle-databaseconstraints
Ross Knight
Ross Knight

Reputation: 39

How to use correctly use REGEXP_LIKE in a constraint

I want to use a constraint that requires the input to be 'SUB' followed by 4 digits. So far this is what ive got but its throws this error.

CREATE TABLE subscriptionsTable(
subID number PRIMARY KEY,
channelID number,
userID number,
subStartDate DATE NULL, 
subEndDate DATE NULL,
subCode VARCHAR(10) NOT NULL,
CONSTRAINT c_subCode (check REGEXP_LIKE(subCode,['SUB{3}[0-9][0-9][0-9][0-9]'))
);

ERROR at line 8:
ORA-00904: : invalid identifier

Upvotes: 0

Views: 92

Answers (1)

Popeye
Popeye

Reputation: 35900

There is a syntax issue in the CHECK constraint and your regex pattern needs a small change.

You need the following code:

CREATE TABLE SUBSCRIPTIONSTABLE(
    SUBID          NUMBER PRIMARY KEY,
    CHANNELID      NUMBER,
    USERID         NUMBER,
    SUBSTARTDATE   DATE NULL,
    SUBENDDATE     DATE NULL,
    SUBCODE        VARCHAR(10) NOT NULL,
    CONSTRAINT     C_SUBCODE
CHECK ( REGEXP_LIKE ( SUBCODE, '^(SUB)[0-9]{4}' ) ) ); -- changes here

Upvotes: 1

Related Questions