oracle check regexp_like

Question

hy,

I am trying to put a constraint with REGEXP_LIKE in oracle, but i keep geting a ORA-00920: invalid relational operator

error, here is my code (the error is at the end of the ck_files_name constraint

CREATE TABLE files(
  idFile INT PRIMARY KEY,
  idParent INT REFERENCES files,
  name VARCHAR2(256),
  type CHAR(1),

  CONSTRAINT ck_files_name  CHECK REGEXP_LIKE(name, '[^\.]'),    -- error ORA-00920:    invalid relational operator
  CONSTRAINT ck_files_type  CHECK type IN ('d', 'f'),
  CONSTRAINT ck_files_idFile_idParent CHECK (idFile <> idParent),
  CONSTRAINT uq_files_idFile_name UNIQUE (idParent, name)
);

Am I doing something wrong, or does it have to do with my oracle version (oracle 10g xe) ?

René Nyffenegger · Accepted Answer · 2011-01-08 00:05:40Z

6

You have to put parantheses after the check keyword.

The following works, at least with Oracle 11, R2.

CREATE TABLE files(
  idFile INT PRIMARY KEY,
  idParent INT REFERENCES files,
  name VARCHAR2(256),
  type CHAR(1),
  CONSTRAINT ck_files_name  CHECK (REGEXP_LIKE(name, '[^\.]')),
  CONSTRAINT ck_files_type  CHECK (type IN ('d', 'f')),
  CONSTRAINT ck_files_idFile_idParent CHECK (idFile <> idParent),
  CONSTRAINT uq_files_idFile_name UNIQUE (idParent, name)
);

answered Jan 8, 2011 at 0:05

René Nyffenegger

40.9k36 gold badges180 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chandu · Accepted Answer · 2011-01-08 00:07:16Z

0

Paranthesis is missing. Try this:

CREATE TABLE files(
  idFile INT PRIMARY KEY,
  idParent INT REFERENCES files,
  name VARCHAR2(256),
  type CHAR(1),

  CONSTRAINT ck_files_name  (CHECK REGEXP_LIKE(name, '[^\.]')),    
  CONSTRAINT ck_files_type  (CHECK type IN ('d', 'f')),
  CONSTRAINT ck_files_idFile_idParent (CHECK (idFile <> idParent)),
  CONSTRAINT uq_files_idFile_name UNIQUE (idParent, name)
);

answered Jan 8, 2011 at 0:07

Chandu

83.2k19 gold badges137 silver badges136 bronze badges

Collectives™ on Stack Overflow

oracle check regexp_like

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related