What is the best way to modify CHECK constraint on table in oracle?-CodePudding

There is already existing table in database. Which contain the check constraint on one of the column. I want to update that constrain without affecting the DB.

CodePudding user response：

Use alter to drop and readd check constraint

     Alter table tabname
     Drop constraint constraint_name;
     Alter table tabname
     Add constraint constraint_name check(id<100);

CodePudding user response：

First Drop the constraint and then add another.

For example:

ALTER table table_name drop constraint constraint_name;

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition );

CodePudding user response：

Although the answers provided are enough, I wanted to show you what happens when you have a very big amount of rows in your table , and the options you might have for the constraint you want to add.

Scenario

SQL> create table t1 ( c1 number generated always as identity start with 1 increment by 1 , c2 varchar2(10) ) ;

Table created.

SQL> alter table t1 add constraint chk_c2_val check (  c2 in ( 'AAAAAAAAAA' , 'BBBBBBBBBB' ) );

Table altered.

Now we will insert a huge amount of rows satisfying the existing constraint, let's make even the number of rows with the two strings by using even and odd values.

SQL> declare
  2  begin
  3  for i in 1 .. 10000000
  4  loop
  5   insert into t1 ( c2 ) values ( case when mod(i,2)=0 then 'AAAAAAAAAA' else 'BBBBBBBBBB' end );
  6  end loop;
  7  commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 ;

  COUNT(*)
-----------
   10000000

Now let's drop the constraint

SQL> set timing on 
SQL> SQL> alter table t1 drop constraint chk_c2_val ;

Table altered.

Elapsed: 00:00:00.04

Being in this current situation, if we know that the constraint we are going to create satisfies already the existing data, we could get an advantage from that:

SQL> alter table t1 add constraint chk_c2_val_1 check ( c2 like 'AAAAAAA%' or c2 like 'BBBBBB%' ) enable novalidate ;

Table altered.

Elapsed: 00:00:00.03
SQL> alter table t1 add constraint chk_c2_val_2 check ( c2 like 'A%' or c2 like 'B%' ) enable validate ;

Table altered.

Elapsed: 00:00:04.68

As you can see, the second one is much slower than the first one, obviously, as the first one is not checking any of the existing rows. It will do for the new or modified rows, though.

ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.

ENABLE NOVALIDATE means the constraint is checked for new or modified rows, but existing data may or may not violate the constraint as it is not validated.

Summary

If you are sure that the new constraint satisfies the existing data, and you have a huge amount of rows in your table, it is sometimes worth to use ENABLE NOVALIDATE as the constraint will not validate the existing data.