在oracle中修改CHECK约束的最好方法是什么?

Question

数据库中已经存在一个表。其中包含对其中一列的检查约束。我想在不影响数据库的情况下更新约束。

Answer 1

使用alter删除和读取check约束

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

Answer 2

先删除约束，然后再添加一个。

例如:

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

Answer 3

虽然提供的答案已经足够了，但是我想向您展示当您的表中有非常多的行时会发生什么，以及您想要添加约束的选项。

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.

现在我们将插入大量满足现有约束的行，让我们通过使用even和odd值来使两个字符串的行数相等。

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

现在我们去掉约束

SQL> set timing on 
SQL> SQL> alter table t1 drop constraint chk_c2_val ;
Table altered.
Elapsed: 00:00:00.04

在当前的情况下，如果我们知道我们要创建的约束已经满足了现有的数据，我们可以从中获得优势:

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

可以看到，第二个比第一个慢得多，显然，因为第一个没有检查任何现有的行。但是，它可以用于新行或修改的行。

启用验证与ENABLE相同。检查约束并保证对所有行都有效。

使已经表示检查约束是否新增或已修改

，但现有数据可能违反约束，也可能不违反约束。

如果您确定新的约束满足现有数据，并且您的表中有大量的行，那么有时值得使用ENABLE NOVALIDATE，因为约束将不验证现有数据。