Disable validate [message #325790] |
Sun, 08 June 2008 20:10 |
shrinika
Messages: 306 Registered: April 2008
|
Senior Member |
|
|
Hello,
Here is the oracle version i am using.
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
I have good understanding of enable novalidate option in constraints. But i am not able to understand where/when exactly disable validate option is useful?
SQL> create table temp(no number);
Table created.
SQL> alter table temp add primary key(no) disable validate;
Table altered.
SQL> insert into temp values(1);
insert into temp values(1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.SYS_C003052)
disabled and validated
When we disable the constraint with disable validate option, it would not let us to insert/update/delete the data in table. But what circumstances, it will be useful??
I would be pleased if any one could answer for this.
|
|
|
Re: Disable validate [message #325792 is a reply to message #325790] |
Sun, 08 June 2008 21:33 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
It is answered in the documentation, in http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2062567:
Quote: | 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.
|
|
|
|
|