I run the command on Oracle 11.2.0.1 on Redhat 5.2
SQL> alter table agency add constraint agency_agcy_ori unique (agcy_ori) ENABLE NOVALIDATE;
alter table agency add constraint agency_agcy_ori unique (agcy_ori) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (DEVPF.AGENCY_AGCY_ORI) - duplicate keys found
I knew the table has duplicate and null in the column which I want to add unique constraint. Using NOVALIDATE, I expect it tolarates the exist duplicate and valid new added data for uniqueness. Because it is stated in Oracle DOc B28286 that (page 8-15)
ENABLE NOVALIDATE ensures that all new DML operations on the constrained
data comply with the constraint. This clause does not ensure that existing data in
the table complies with the constraint
However, my database does not agree with this. Did I misunderstood the doc?
Other related questions
1) I have two rows with the column agcy_ori is NULL. Would that be considered duplicate and fails VALIDATE when create a unique constraint.
2) The same doc also states (page 8-16)
DISABLE VALIDATE disables the constraint and drops the index on the constraint,
but keeps the constraint valid. This feature is most useful in data warehousing
situations, because it lets you load large amounts of data while also saving space
by not having an index. This setting lets you load data from a nonpartitioned table
into a partitioned table using the exchange_partition_clause of the ALTER
TABLE statement or using SQL*Loader. All other modifications to the table
(inserts, updates, and deletes) by other SQL statements are disallowed
This raises more questions:
a) DISABLE VALIDATE an unique constraint drop the unique index but still validate uniqueness. This is saying that te uniqueness is maintained without an unique index. If that is possible, why ENABLE an unique constraint need to create an unique index?
b) The last setense 'All other modifications to the table by other SQL statements are disallowed'. This actually make the table read only except for partition/nonpartition and SQL*LOADER. This can be a useful feature but I do not see the logic here: Cannot update column2 of the table just because a constraint in column 10 is DISABLE VALIDATE.
Hope people can help me here. Thanks.