i have a Question, "why have i create first a new constraint and then delete the old constraint? Why in this order?"
i want underline that my Question is for the sequence, why this sequence?
select * from user_constraints;
alter table unipc add constraint nrcheck check(nr>10);
alter table unipc drop constraint SYS_C6969254;
You do not have to have that order of operations. You can delete the old constraint and then create the new constraint and it is a perfectly valid set of commands.
CREATE TABLE table_name (
id NUMBER CONSTRAINT constraint_name1 CHECK ( id > 10 )
);
INSERT INTO table_name ( id ) VALUES ( 11 );
ALTER TABLE table_name DROP CONSTRAINT constraint_name1;
ALTER TABLE table_name ADD CONSTRAINT constraint_name2 CHECK ( id > 9 );
However, if you drop the constraint without a replacement constraint being in place then there will be a period of time when it is possible that an INSERT or UPDATE statement could modify the table so that it contains data that is invalid against the constraint and creation of the new constraint would fail.
CREATE TABLE table_name (
id NUMBER CONSTRAINT constraint_name1 CHECK ( id > 10 )
);
INSERT INTO table_name ( id ) VALUES ( 11 );
ALTER TABLE table_name DROP CONSTRAINT constraint_name1;
INSERT INTO table_name ( id ) VALUES ( 4 );
Then:
ALTER TABLE table_name ADD CONSTRAINT constraint_name2 CHECK ( id > 9 );
Fails with:
ORA-02293: cannot validate (SCHEMA_NAME.CONSTRAINT_NAME2) - check constraint violated
Whereas, if you create the new constraint before dropping the old constraint then the invalid data cannot be inserted.
The database does not enforce this ordering.
My guess is that guidance is to ensure that one of the constraints (at least) is always in place on the table. So, inserts and updates that take place between the two alter table statements have a constraint on the table.
