ORA-00907 when trying to create a CHECK constraint

Question

I need your help with this error:

ORA-00907 on Check CONSTRAINT

Query:

CREATE TABLE S_NEWS.T_UTILISATEUR_USR ( 
  USR_ID                        INTEGER      NOT NULL  PRIMARY KEY,
  USR_MAIL                      VARCHAR(256) NOT NULL,
  USR_TITRE      CHAR(6)      NULL DEFAULT 'M.'CHECK (USR_TITRE IN ('M.' , 'Mlle.','Mme.' )),  
  USR_NOM                       CHAR(32)     NOT NULL,
  USR_PRENOM                    VARCHAR(32)  NULL,
  USR_ORGANISATION              VARCHAR(128) NULL
);

Answer 1

The error message is

ORA-00907: missing right parenthesis

It almost always points to a syntax error rather than a missing bracket. In this case the parser is objecting to the order of the elements in your column definition. Specifically, the DEFAULT clause must come before the CONSTRAINT clause, which includes the NULL/NOT NULL declaration. So try

USR_TITRE CHAR(6) DEFAULT 'M.'CHECK (USR_TITRE IN ('M.' , 'Mlle.','Mme.' )) NULL

Incidentally, you're going to a problem with that constraint. A CHAR datatype is always padded to the declared length. Thus if you enter 'M.' into the column it will pad out to 'M. ', which value will cause the constraint to hurl an exception. I suggest you use VARCHAR2(6) instead.

CHAR declarations are almost always a mistake, just a bug waiting to happen.