ORA-00907尝试创建CHECK约束时 [英] ORA-00907 when trying to create a CHECK constraint

问题描述

此错误我需要您的帮助:

I need your help with this error:

ORA-00907检查约束

ORA-00907 on Check CONSTRAINT

查询:

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

推荐答案

错误消息是

ORA-00907: missing right parenthesis

几乎总是指向语法错误，而不是缺少括号.在这种情况下，解析器将反对列定义中元素的顺序.具体来说，DEFAULT子句必须位于CONSTRAINT子句之前，该子句包含NULL/NOT NULL声明.所以尝试

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

顺便说一句，您将遇到该约束问题.始终将CHAR数据类型填充为声明的长度.因此，如果您输入"M".进入列，它将填充到'M. '，该值将导致约束抛出异常.我建议您改用VARCHAR2(6).

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声明几乎总是一个错误，只是一个等待发生的错误.

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

这篇关于ORA-00907尝试创建CHECK约束时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！