I need your help with this error:
ORA-00907 on Check C开发者_开发问答ONSTRAINT
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
);
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.
精彩评论