Disable PK Constraint ORA-00054 生成子表的Constraint

Disable PK cascade如果子表上有DML会导致一些错误，所以最好的办法是先disable FK 再disable PK，什么事务都没有，可以考虑cascade.

Trying to Disable PK Constraint Gives Error ORA-2297 And ORA-2433 (Doc ID 2564824.1)    To BottomTo Bottom    

In this Document
Symptoms
Changes
Cause
Solution

APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.
SYMPTOMS
Trying to disable constraints on parent table gives error ORA-2297.

ORA-2297: cannot disable constraint (string.string) - dependencies exist
  Cause: an alter table disable constraint failed because the table has foreign keys that are dependent on this constraint.
  Action: Either disable the foreign key constraints or use disable cascade

Then try to drop with cascade clause and get error:

ALTER TABLE NAME DISABLE PRIMARY KEY CASCADE

ORA-02433: cannot disable primary key - primary key not defined for table

CHANGES
 

CAUSE
ORA-02433: cannot disable primary key - primary key not defined for table

Indicates no primary key, but unique constraint on child table.

 

SOLUTION
Disable the constraint directly on child table first, then disable on parent table.

Check the constraint on each with following sql:

SELECT p.table_name "Parent Table", c.table_name "Child Table",
  p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
  FROM user_constraints p
  JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
  WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
  AND c.constraint_type = 'R'
  AND p.table_name = UPPER('&table_name');

--- 指定表，查看对应的主表 及子表。
  select 'alter table '||owner||'.'|| table_name || ' disable constraint '|| CONSTRAINT_NAME||';' ,owner, table_name , CONSTRAINT_NAME,status,VALIDATED
  from  cdb_constraints dc where (dc.r_owner,dc.R_CONSTRAINT_NAME ) 
in (select owner,CONSTRAINT_NAME from cdb_constraints dc where dc.table_name=' ' 
and dc.owner=' ' and dc.constraint_type in('P','U'));
 
--alter table DATAMIGCP.STG_SOURCE disable constraint FK_SOURCE_WORK
 
 
select * from  cdb_constraints dc where (owner,CONSTRAINT_NAME ) in (
select r_owner, R_CONSTRAINT_NAME from cdb_constraints dc 
where dc.table_name=' ' and dc.constraint_type='R' and owner=' ');

SELECT p.owner,p.table_name "Parent Table",  p.constraint_name "Parent Constraint",
c.owner,c.table_name "Child Table",c.constraint_name "Child Constraint"
  FROM dba_constraints p
  JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
  WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
  AND c.constraint_type = 'R'
  AND p.table_name = UPPER(' ') and p.owner=' ' and c.r_owner=p.owner;   

------Oracle 提供的脚本有bug，如果存在不同schema但是同名的Constraint name，会多----

Script
rem 
rem Description   This SQL script generates referential constraints 
rem               for a table.  (Not including primary key info) 
rem 
rem Argument(s)   Spool File Name, Table name 
rem 

SPOOL &spool..ref 
  
set PAGESIZE 0 
set FEEDBACK OFF 
set ECHO OFF 
set VERIFY OFF 
set LINESIZE 132 
COLUMN DUMMY_1 NOPRINT FORMAT A30 
COLUMN DUMMY_2 NOPRINT FORMAT 9 
COLUMN DUMMY_3 NOPRINT FORMAT 99 
COLUMN COMMAND FORMAT A80 
  
 
SELECT 'ALTER TABLE '||C.TABLE_NAME||' ADD CONSTRAINT '|| 
       C.CONSTRAINT_NAME||' FOREIGN KEY (' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 1 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_CONSTRAINTS C,SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND C.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
  FROM SYS.DBA_CONSTRAINTS
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT DECODE(CC.POSITION,1,NULL,',') || CC.COLUMN_NAME COMMAND, 
       CC.CONSTRAINT_NAME DUMMY_1, 2 DUMMY_2, CC.POSITION DUMMY_3 
  FROM SYS.DBA_CONS_COLUMNS CC,SYS.DBA_CONSTRAINTS C, SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
       AND CC.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT ') REFERENCES '||I.TABLE_NAME||' (' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 3 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_INDEXES I, SYS.DBA_CONSTRAINTS C 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT DECODE(IC.COLUMN_POSITION,1,NULL,',') ||  
       IC.COLUMN_NAME COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1, 4 DUMMY_2, 
       IC.COLUMN_POSITION DUMMY_3 
  FROM SYS.DBA_IND_COLUMNS IC,  
       SYS.DBA_CONSTRAINTS C,SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND IC.INDEX_NAME = I.INDEX_NAME 
       AND C.CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       UNION 
SELECT ')'||DECODE(C.DELETE_RULE,'CASCADE', 
       ' ON DELETE CASCADE',NULL)|| 
       DECODE(C.STATUS,'DISABLED',' DISABLE',NULL)||';' COMMAND, 
       C.CONSTRAINT_NAME DUMMY_1 ,5 DUMMY_2, 0 DUMMY_3 
  FROM SYS.DBA_CONSTRAINTS C, SYS.DBA_INDEXES I 
 WHERE I.TABLE_NAME = UPPER('&&TABLE_NAME') 
       AND C.R_CONSTRAINT_NAME = I.INDEX_NAME 
       AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME  
  FROM SYS.DBA_CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'R') 
       ORDER BY 2,3,4; 

spool off 
exit 
 


==============
Sample Output:
==============

SQL> connect scott/<PASSWORD>
Connected.
SQL> @savedscript
Enter value for spool: spool817.txt
Enter value for table_name: LU_POSTAL_CD1

ALTER TABLE YYY ADD CONSTRAINT YYY_POSTAL_FK FOREIGN KEY (
POSTAL_KEY
) REFERENCES LU_POSTAL_CD1 (
POSTAL_CD
);