How can I change check constraint in this condition?
I have a table brucetest, it has 6 column, in one of its column called f, I want to change the check constraint from collection (2,3,4,5) into collection (2,3,4). That means delete 5. But avoid to user insert into a record with f=5, I just lock the table, the script is:
1. LOCK TABLE brucetest IN EXCLUSIVE MODE;
2.delete from brucetest where f=5;
3.alter table brucetest drop constraint tbt;
4.alter table brucetest add constraint tbt check(f in (2,3,4));
5.commit;
You know if we have the insert sentence after sentence 2 or 3, but before 4 like:
insert into brucetest (a,ldate,c,d,e,f) values('dddd',sysdate,null,null,null,5);
because of alter table implicit commit, the lock table will be ended after sentence 3, so sentence 4 will cause an error:
Error starting at line 2 in command:
alter table brucetest add constraint tbt check(f in (2,3,4))
Error report:
SQL Error: ORA-02293: cannot validate (TBT) - check constraint violated
So how can I do under this condition ?
thx