Skip to main content

Search

Welcome to Tibero GTS!

Error Phenomenon Where Only PK Information Is Deleted When Dropping PK of Table Referenced by Self FK

Document Type | Troubleshooting

Category | Patch/Upgrade

Applicable Product Versions | 6FS06, 6FS07

Document Number | TPATS026

 

Issue

When dropping the PK (Primary Key) of a table that is referenced by a self FK (Foreign Key), only the PK information is deleted, causing an issue.
 

Reproduction Scenario

create table test ( no number , yy varchar(4), mm varchar(4), par_no number);
create unique index pk_test on test (no);

alter table test add constraint pk_test primary key (no) using index pk_test;


alter table test add constraint fk_self_test 
foreign key (par_no) references test (no);


alter table test drop primary key;     โ†’ This PK should not be deletable as it is referenced by FK, but it is deleted


select * from dba_constraints where table_name='TEST';  โ†’ FK seems to be deleted as well (FK information remains in system dictionary)
0 row selected.


insert into test values (1,2021,1,1); 
TBR-12035: Internal error -528005 occurred.

 

 

Cause

If there is an FK (Foreign Key) referencing the PK (Primary Key), the FK must be deleted before dropping the PK. However, only the PK was dropped, leaving the FK information in the system's internal dictionary, which caused the error. 
 
 
 
 

Solutions

Apply the patch to resolve the issue. (Applied Patch: 241435a)
  • Patch 241435a: This patch modifies the system so that when there is a self references constraint, the referenced primary key cannot be dropped.
Caution
Apply the patch through technical support provided by TmaxTibero.
 
Note
The method to delete FK information from already affected tables is as follows. 
Execute as SYS account (Drop problematic FKs for all users)
   SET SERVEROUTPUT ON;

  DECLARE
    CURSOR csr IS
       SELECT c.username, a.obj_id, a.name
       FROM _dd_con a, _dd_obj b, dba_users c
       WHERE a.obj_id = b.obj_id
         AND b.owner_id = c.user_id
         AND a.ref_obj_id = a.obj_id
         AND a.ref_con_id NOT IN (SELECT con_id FROM _dd_con);


tbl_obj_id        NUMBER;
con_name          VARCHAR2(1000);
tbl_name          VARCHAR2(1000);
username          VARCHAR2(100);
drop_ref_con_ddl  VARCHAR2(1000);

  BEGIN
    OPEN csr;


LOOP
   FETCH csr INTO username, tbl_obj_id, con_name;
   EXIT WHEN csr%NOTFOUND;

   SELECT name INTO tbl_name
   FROM sys._dd_obj
   WHERE obj_id = tbl_obj_id;

   DBMS_OUTPUT.PUT_LINE('USERNAME : ' || username ||
                        ' TABLE : ' || tbl_name ||
                        ' OBJ_ID : ' || tbl_obj_id ||
                        ' SELF_REF_CONSTRAINT : ' || con_name ||
                        ' IS INCONSISTENT');

   drop_ref_con_ddl := 'ALTER TABLE ' || username || '.' || tbl_name ||
                       ' DROP CONSTRAINT ' || con_name;

   EXECUTE IMMEDIATE drop_ref_con_ddl;

   DBMS_OUTPUT.PUT_LINE('USERNAME : ' || username ||
                        ' TABLE : ' || tbl_name ||
                        ' OBJ_ID : ' || tbl_obj_id ||
                        ' SELF_REF_CONSTRAINT : ' || con_name ||
                        ' DROPPED');
END LOOP;

CLOSE csr;

 END;
 /
 
 
 

 

 

Related to