请问这个约束怎么建立??

[SimerJoe]

我要一个字段全部是由'0'或'1'组成的,并且插入的时候长度一定要为20
CITEMID in ('0','1','2','3','4','5','6','7','8','9') and length(CITEMID) = 20
这样对吗?好像不行哦

[SimerJoe]

说错了,应该是'0''1''2'一直到'9'的字符都可以,不光是'0'和'1'

[ATGC]

lengthb(translate(CITEMID,'01'||CITEMID,'00'))=20
zheyang ?我没试过，你自己测试一下吧

[ATGC]

create table aa (CITEMID VARCHAR2(20) CHECK (LENGTHB(translate(CITEMID,'0123456789'||CITEMID,'0000000000'))=20));

INSERT INTO AA VALUES('0123456789012345678A')
*
ERROR at line 1:
ORA-02290: check constraint (ZHU.SYS_C0019589) violated

1* INSERT INTO AA VALUES('01234567890123456789')
SQL> /

1 row created.

SQL> EDIT
Wrote file afiedt.buf

1* INSERT INTO AA VALUES('0123456789012345678')
SQL> /
INSERT INTO AA VALUES('0123456789012345678')
*
ERROR at line 1:
ORA-02290: check constraint (ZHU.SYS_C0019589) violated


SQL> EDIT
Wrote file afiedt.buf

1* INSERT INTO AA VALUES('012345678901234567890')
SQL> /
INSERT INTO AA VALUES('012345678901234567890')
*
ERROR at line 1:
ORA-01401: inserted value too large for column