Regular Expression in a Check Constraint

View All Scripts Login to Run Script

Script Name Regular Expression in a Check Constraint
Description Demonstration of increased strictness on check constraints. Regular expressions are not permitted in constraints or function-based indexes because they are not deterministic across NLS settings. Additional background for this script can be found at https://asktom.oracle.com/pls/apex/asktom.search?tag=adding-constraint-with-regexp-replace-fails-201803
Area SQL General
Contributor Enveryn
Created Friday March 23, 2018

Statement 1

CREATE TABLE Persons ( 
    PersonID int, 
    LastName varchar(255), 
    FirstName varchar(255), 
    Address varchar(255), 
    City varchar(255), 
	Zip varchar(30) 
)

Table created.

Statement 2

ALTER TABLE Persons ADD CONSTRAINT PERSONS_CHECK_CITY CHECK (REGEXP_REPLACE(Zip, '(\d+)', '') is null) ENABLE

ORA-02436: date or system variable wrongly specified in CHECK constraint

More Details: https://docs.oracle.com/error-help/db/ora-02436

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database