可以变通一下 SQL> SQL> create table abcd(id number,type varchar2(2),schdeule_id char(20), 2 constraint abcd_1 foreign key(schdeule_id) references abc(a), 3 constraint abcd_2 check(type in ('aa','bb','cc') and case type when 'aa' then schdeule_id else '1' end is not null));
SQL> alter table plan_schedule add constraint CONT_SCHEDULE_TYPE_SCHEDULE_ID check(SCHEDULE_TYPE IN('PlanSchedule','Annual','Month')and case SCHEDULE_TYPE when 'PlanSchedule' then CURRENT_STATUS_ID else '1' end is not null);alter table plan_schedule add constraint CONT_SCHEDULE_TYPE_SCHEDULE_ID check(SCHEDULE_TYPE IN('PlanSchedule','Annual','Month')and case SCHEDULE_TYPE when 'PlanSchedule' then CURRENT_STATUS_ID else '1' end is not null)ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR 数据类型CURRENT_STATUS_ID 是number,这个要怎么改
SQL> alter table plan_schedule add constraint CONT_SCHEDULE_TYPE_SCHEDULE_ID check(SCHEDULE_TYPE IN('PlanSchedule','Annual','Month')and case SCHEDULE_TYPE when 'PlanSchedule' then CURRENT_STATUS_ID else 1 end is not null);Table altered把单引号去掉可以成功了
自己也happy一下!呵呵!
SQL>
SQL> create table abcd(id number,type varchar2(2),schdeule_id char(20),
2 constraint abcd_1 foreign key(schdeule_id) references abc(a),
3 constraint abcd_2 check(type in ('aa','bb','cc') and case type when 'aa' then schdeule_id else '1' end is not null));
Table created
SQL> insert into abcd values(1,'dd',null);
insert into abcd values(1,'dd',null)
ORA-02290: 违反检查约束条件 (W.ABCD_2)
SQL> insert into abcd values(1,'bb',null);
1 row inserted
SQL> insert into abcd values(2,'aa',null);
insert into abcd values(2,'aa',null)
ORA-02290: 违反检查约束条件 (W.ABCD_2)
SQL> insert into abcd values(3,'aa','2');
1 row inserted
SQL> insert into abcd values(4,'aa','123');
insert into abcd values(4,'aa','123')
ORA-02291: 违反完整约束条件 (W.ABCD_1) - 未找到父项关键字
SQL>
若type不是'aa',则取'1',这个'1'也可以用其他的非空字符代替,类型要与else前取的schdeule_id一致,使非空判断恒成立
若type 为'aa',则用schdeule_id来判断是否非空
SQL> alter table plan_schedule add constraint CONT_SCHEDULE_TYPE_SCHEDULE_ID check(SCHEDULE_TYPE IN('PlanSchedule','Annual','Month')and case SCHEDULE_TYPE when 'PlanSchedule' then CURRENT_STATUS_ID else '1' end is not null);alter table plan_schedule add constraint CONT_SCHEDULE_TYPE_SCHEDULE_ID check(SCHEDULE_TYPE IN('PlanSchedule','Annual','Month')and case SCHEDULE_TYPE when 'PlanSchedule' then CURRENT_STATUS_ID else '1' end is not null)ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR
数据类型CURRENT_STATUS_ID 是number,这个要怎么改