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

Answer 1

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

Answer 2

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