ENABLE NOVALIDATE a constraint

 

Specify ENABLE if you want the constraint to be applied to the data in the table.

If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specify KEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.

You can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

 

非空索引特性

1) use ENABLE NOVALIDATE causes validating not only future data but also existing data if the constraint is created with an unique index.
2) To create a unique constraint that only validate future data, using a non unique index like the comments of AliD and Hemant.
3) The non unique index must not be a functional index like lower().
4) multiple rows with the unique column NULL are not considered as duplicate, and hence not violate unique constraint,
5) DISABLE means not check for uniqueness for new data, that is, Oracle treat the table as there is not a constraint defined.

红色字体部分总结有误：

1. Such a definition can be unique but not a Primary Key Constraint.
2. If you have a multi-column index and one of the columns contains NULLs while the other column contains duplicated values for two rows, the UNIQUE INDEX creation fails.
(ie. an index on the first column alone can be UNIQUE enven though two rows are NULLs but if you add a second column with duplicate values, the index is no longer Unique).

 

See http://hemantoracledba.blogspot.com/2010/02/something-unique-about-unique-indexes.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21818314/viewspace-693198/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21818314/viewspace-693198/