Having played with this (based on your previous writings), my understanding is that the constraint you want to temporarily violate must be deferrable. To temporarily violate it though, you need to put your session into a state where the constraints are deferred, and then you can manually set them bad to immediate enforcement, or let the commit/rollback restore the state for you once you are done with the temporary violation.

SQL> CREATE TABLE dept
  2     (deptno   NUMBER NOT NULL PRIMARY KEY ,
  3      deptname VARCHAR2(30) NOT NULL );

Table created.

SQL> 
SQL> CREATE TABLE emp
  2     (empno   NUMBER NOT NULL PRIMARY KEY,
  3      empname  VARCHAR2(20) NOT NULL,
  4      deptno   NUMBER,
  5         CONSTRAINT emp_fk1 foreign key (deptno) references dept(deptno) DEFERRABLE);

Table created.

SQL> 
SQL> insert into dept values (10, 'Sales');
1 row created.

SQL> insert into emp values (1, 'Joe', 10);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> -- see if we can do it with just deferrable constraints
SQL> begin
  2   update dept set deptno = 100 where deptno = 10;
  3   update emp set deptno = 100 where deptno = 10;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_FK1) violated - child record found
ORA-06512: at line 2


SQL> -- no - we need to set the constraints to deferred in this session first
SQL> -- in pl/sql block because most applications would typically use it this way
SQL> begin
  2  Execute immediate 'SET CONSTRAINTS ALL DEFERRED';
  3  update dept set deptno = 100 where deptno = 10;
  4  update emp set deptno = 100 where deptno = 10;
  5   -- set them back on again now that we are done
  6   -- else they are reset to default (immediate)
  7   -- on commit/rollback
  8  Execute immediate 'SET CONSTRAINTS ALL IMMEDIATE';
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from dept;

    DEPTNO DEPTNAME
---------- ------------------------------
       100 Sales

SQL> select * from emp;

     EMPNO EMPNAME                  DEPTNO
---------- -------------------- ----------
         1 Joe                         100

SQL>  

Hi Tom,
I'm trying to make a constraint (that is NOT DEFERRABLE) back to DEFERRABLE but getting the following error. Looked at the documentation for the MODIFY CONSTRAINT clause and it seems to suggest the same syntax that I am using. Is there something that I missed (or did wrong)?

How else to do this?
Thanks,

SQL> alter table emp add constraint fk_emp_deptno foreign key (deptno) references dept(deptno)
  2  not deferrable initially immediate ;

Table altered.

SQL>
SQL> alter table emp modify constraint fk_emp_deptno deferrable ;
alter table emp modify constraint fk_emp_deptno deferrable
                                                *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL>
 

SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL>
 

Respected Tom,

I have a materialized view named my_mview having the primary key column employee_code in a database say Y. Suppose this my_mview is completely refreshed every night and it gets the information of employees from several tables of any other database say Z. In database Y there is a table emp_north having column employee_code. I wanna reference this column to employee_code in my_mview (my_mview_employee_code_pk) in the same database. When I establish this using foreign key constraint emp_north_employee_code_fk. It suppresses the refresh of the materialized view...

SQL> exec dbms_mview.refresh('my_mview');
BEGIN dbms_mview.refresh('my_mview'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

How can this type of integrity be enforced on emp_north in Oracle9i?

Regards, 

hi tom,
i'm very consfused with these constraint states,plz check this example once..
SQL> create table gg (no number primary key initially deferred);

Table created.

SQL> desc gg
Name Null? Type
------------------------------- -------- ----
NO NUMBER

SQL> insert into gg values(1);

1 row created.
SQL> insert into gg values(1);

1 row created.

SQL> select * from gg;

NO
-----------
1
1


SQL> insert into gg values(1);

1 row created.

SQL> alter table gg
2 modify primary key enable novalidate;
alter table gg
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.SYS_C0043633) violated

SQL> alter table gg modify primary key enable novalidate;

Table altered.
SQL> insert into gg values(1);

1 row created.
SQL> insert into gg values(1);

1 row created.
SQL> alter table gg modify primary key disable validate;
alter table gg modify primary key disable validate
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.SYS_C0043633) violated

SQL> alter table gg modify primary key disable validate;

Table altered.

SQL> select * from gg;
no rows selected.

SQL> insert into gg values(1);
insert into gg values(1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.SYS_C0043633) disabled and
validated.

y tom,it has to insert the data atleast once into the table but it not doing y as the table constains no data...

tom i understood from the doc that,
enable novalidate-enables the constraint but the constraint is not checked with the data..this is happening my example above also..
disable validate-disables the constraint but it checks with the data its not happening here at all its not allowing me insert even one row of data...
1. plz explain me
2.tom, is there any way to force the primary key,unique or check constraint on table already contains some duplicate,null values...this can be done to not null constraint by using ENFORCE keyword...

plz forgive me if there is any ignorance and kindly expalin me these concepts 

Hi Tom,

this is my test case:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production

SQL> drop table child;

Table dropped.

SQL> drop table parent;

Table dropped.

SQL> create table parent (x number primary key);

Table created.

SQL> create table child (x number);

Table created.

SQL> alter table child
  2   add constraint fk foreign key(x)
  3   references parent
  4   deferrable initially immediate;

Table altered.

SQL> select constraint_name, deferrable, deferred from user_constraints
  2   where constraint_name = 'FK';

CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
FK                             DEFERRABLE     IMMEDIATE

SQL> set constraint fk deferred;

Constraint set.

SQL> select constraint_name, deferrable, deferred from user_constraints
  2   where constraint_name = 'FK';

CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
FK                             DEFERRABLE     IMMEDIATE


Shouldn't DEFERRED now be something other than 'IMMEDIATE', e.g. 'DEFERRED'?
How can I find out if a constraint has been set in deferred state?

Thanks, Urs 

alter table big_table parallel 4 ;

alter session enable parallel dml;

exec dbms_application_info.set_client_info('VALIDATE_CONSTRAINT');

alter table big_table
modify id constraint id_not_null
not null novalidate ;

alter table big_table
modify constraint id_not_null
validate;

select sid,serial#,event,sql_id,
   ( select distinct sql_text from v$sql t1 where t1.sql_id = t2.sql_id ) sql_text,t2.last_call_et
 from gv$session t2
where client_info='VALIDATE_CONSTRAINT';

select /*+ all_rows ordered */ A.rowid, :1, :2, :3 
from
 "RAJESH"."BIG_TABLE" A where not ( "ID" IS NOT NULL)

Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  PX COORDINATOR  (cr=145999 pr=145476 pw=0 time=291297275 us)
      0   PX SEND QC (RANDOM) :TQ10000 (cr=145999 pr=145476 pw=0 time=291297271 us)
      0    PX BLOCK ITERATOR (cr=145999 pr=145476 pw=0 time=291297265 us)
      0     TABLE ACCESS FULL BIG_TABLE (cr=145999 pr=145476 pw=0 time=291297261 us)

Hi Tom,

I encounter with one strange thing. I found a table_name in all_constraints which does not exists in all_table table_name for same schema. see below.

sql> select table_name from all_tables where owner='BAID_DL_IMP' order by table_name;

table_name
-------------
......
........
BANKRECO_TRAN
BANK_INTEREST
BANK_MAST
BILLAGE_TRAN
BILL_CATG_MAST
BOM_MAST
BS_MAST
CHALLAN_TRAN

sql> select table_name,constraint_type from all_constraints  where owner='BAID_DL_IMP' and constraint_type='P' order by table_name  


table_name      constraint_type
--------------------------------
...........
..................
BANKRECO_TRAN P
BANK_MAST P
BILL_CATG_MAST P
BIN$1jKxqAm3Rj+XpleBlLSUmQ==$0 P << where it come from...
BOM_MAST P
BS_MAST P
CHALLAN_TRAN P

Actually i am trying to create an script to disable the pk constraints on all table. when i execute it it fails.

script query
--------------
select 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name  as sql_string,constraint_name,table_name,*
            from all_constraints
            where constraint_type='P'
            and owner='BAID_DL_IMP' order by table_name

SQL> ALTER TABLE BAID_DL_IMP.BIN$1jKxqAm3Rj+XpleBlLSUmQ==$0 DISABLE CONSTRAINT
  2  BIN$Kt8AaMGsQLe/XNE+XOL3+A==$0;
ALTER TABLE BAID_DL_IMP.BIN$1jKxqAm3Rj+XpleBlLSUmQ==$0 DISABLE CONSTRAINT 
                                      *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
 
Vikas Sharma