2

I am using oracle12C as database and using get_ddl for getting ddl of database objects.Now i have two tables Table1 and Table2 and Table1 has partition, Table2 is using primary constraint of Table1 as reference constraint and also using PARTITIONED BY REFERENCE.
For eg :-

TABLE1 :-

create table parent_emp(
empno      number  primary key,
job        varchar2(20),
sal        number(7,2),
deptno     number(2)
)
partition by list(job)(
  partition p_job_dba values ('DBA'),
  partition p_job_mgr values ('MGR'),
  partition p_job_vp  values ('VP')
);

TABLE2 :-

CREATE TABLE "SECONDARYUSER"."REFERENCE_EMP"(    
   "ENAME" VARCHAR2(10), 
   "EMP_ID" NUMBER, 
   "EMPNO" NUMBER, 
    CONSTRAINT "FK_EMPNO" FOREIGN KEY ("EMPNO")
      REFERENCES "SECONDARYUSER"."PARENT_EMP" ("EMPNO") ENABLE
) 
PARTITION BY REFERENCE ("FK_EMPNO")(
 PARTITION "P_JOB_DBA" , 
 PARTITION "P_JOB_MGR" , 
 PARTITION "P_JOB_VP" )

My issue is I want to disable-enable constraints from Table1 and Table2 but when I execute following script to disable Table1 primary key:

alter table parent_emp disable constraint SYS_C0010720 cascade;

it gives following error:-

02297. 00000 - "cannot disable constraint (%s.%s) - dependencies exist"
*Cause:    an alter table disable constraint failed becuase the table has
           foriegn keys that are dpendent on this constraint.
*Action:   Either disable the foreign key constraints or use disable cascade

So I tried to disable Table2 foreign key constraint and execute the following query:

alter table reference_emp disable constraint FK_EMPNO cascade;

but it gave the following error:-

alter table reference_emp disable constraint FK_EMPNO cascade
Error report:
SQL Error: ORA-14650: operation not supported for reference-partitioned tables

Please suggest me how can I disable-enable constraints in this condition.

Diksha
  • 406
  • 5
  • 20
santosh
  • 435
  • 1
  • 7
  • 24
  • Can you tell us why you want to disable the foreign key constraints? I ask because I was once in a similar situation, and found out that Oracle has optimizations for reference partitioning that may make it unnecessary to disable the constraints. In my case, I found that [direct path writes work even if reference partitioned foreign key constraints are enabled.](https://stackoverflow.com/q/23330143/409172) – Jon Heller Aug 23 '19 at 02:37

0 Answers0