Im using SQL/PL developer and I have a table named Appeal, that has 2 attributs OpenDate and CloseDate. And I want to add a constraint to ensure that open date will be smaller than close date. I have a lot of records in this table.
this is my code:
alter table appeal
add constraint Check_Dates
check (OpenDate < CloseDate)
and I get en error saying: ORA-02293: cannot validate (STSTEM.CHECK_DATES) - check constraint violated
any ieads? Thanx
Your constraint looks right, I have tested it:
create table appeal ( OpenDate date, CloseDate date);
alter table appeal
add constraint Check_Dates
check (OpenDate < CloseDate);
insert into appeal values ( sysdate, sysdate - 1 );
And here the result:
Schema Creation Failed: ORA-02290: check constraint (USER_4_44096.CHECK_DATES) violated
Problem is than you have already rows with OpenDate < CloseDate values in your database. Fix it before create constraint. Look behavior changing sentences order:
create table appeal ( OpenDate date, CloseDate date);
insert into appeal values ( sysdate, sysdate - 1 );
alter table appeal
add constraint Check_Dates
check (OpenDate < CloseDate);
And here your issue:
Schema Creation Failed: ORA-02293: cannot validate (USER_4_E4450.CHECK_DATES) - check constraint violated
Try this alter table appeal add constraint Check_Dates check (OpenDate < CloseDate) ENABLE NOVALIDATE;
You will have check the previous data for errors but any new Data will fall under the CHECK
