原先:
sex char(1) constraint sex_pk check (sex in('M','F'))想改为:
sex char(3) constraint sex_pk check (sex in('M','F'))怎么改啊?
用modify老是报错ORA-02290: 违反检查约束条件
sex char(1) constraint sex_pk check (sex in('M','F'))想改为:
sex char(3) constraint sex_pk check (sex in('M','F'))怎么改啊?
用modify老是报错ORA-02290: 违反检查约束条件
增加字段长度用以下的即可
alter table TABLENAME modify sex char(3)
check约束沿用之前建的
如果不需要,可以drop掉可否把报错的语句贴出来
是否有别的约束
或者是你改成char(3)之后,实际上是不可能满足约束的。因为'M'储存到sex字段后会变成'M ',以空格补齐到3位
你去掉约束试试
至于我的不会报错,可能是版本的问题
出去吃个饭 回来我也试试
shiiwan说他病了
先去掉check才行啊!不然你就改成这样的好了sex char(3) constraint sex_pk check (sex in('M ','F '))
insert into t2 values('H')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0022833) violated
SQL> insert into t2 values('M');1 row created.SQL> insert into t2 values('F');1 row created.SQL> insert into t2 values('f');
insert into t2 values('f')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0022833) violated
SQL> select * from t2;S
-
M
FSQL> alter table t2 modify sex char(3);Table altered. --我的也成功了呀SQL>
SQL> create table t1(sex char(1) constraint sex_pk check (sex in('M','F')));Table createdSQL> insert into t1(sex) values('M');1 row insertedSQL> commit;Commit completeSQL> select * from t1;SEX
---
MSQL> alter table t1 modify sex char(3);alter table t1 modify sex char(3)ORA-02290: 违反检查约束条件 (.)SQL> alter table t1
2 drop CONSTRAINT sex;alter table t1
drop CONSTRAINT sexORA-02443: 无法删除约束条件 - 不存在约束条件SQL> alter table t1
2 drop constraint sex_pk;Table alteredSQL> alter table t1 modify sex char(3);Table alteredSQL> desc t1;
Name Type Nullable Default Comments
---- ------- -------- ------- --------
SEX CHAR(3) Y SQL>
要是再从 char(4)->char(1)
看看到底能不过过!
给予我报错:
cannot decrease column length because some value is too big!
估计 #6楼 说的对,被空格被齐到4了吧~