Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I change check constraint in this condition?

goodbaby99Jun 7 2006 — edited Jun 7 2006
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 5 2006
Added on Jun 7 2006
2 comments
487 views