5313 Referential integrity violation: cannot drop a referenced (Parent) table

Cause:

You cannot drop parent table where child table constraint referring to parent table.

 

Solution:

Example:

 database tmp_test_20171120;

 

CREATE SET TABLE test1,NO FALLBACK ,

          NO BEFORE JOURNAL,

          NO AFTER JOURNAL,

          CHECKSUM = DEFAULT

          (

           a INTEGER NOT NULL,

           b INTEGER,

           c INTEGER)

     UNIQUE PRIMARY INDEX ( a );

             

 *** Table has been created.

 

     CREATE SET TABLE test2 ,NO FALLBACK ,

          NO BEFORE JOURNAL,

          NO AFTER JOURNAL,

          CHECKSUM = DEFAULT

          (

           a1 INTEGER,

           b1 INTEGER,

           c1 INTEGER,

 

     FOREIGN KEY ( a1 ) REFERENCES test1 ( a ))

     PRIMARY INDEX ( a1 );

*** Table has been created.


DROP TABLE test1;

 *** Failure 5313 Referential integrity violation: cannot drop a referenced (Parent) table.

 

Option 1: Drop RI Constraint from child table that pointing to Parent Table

select ChildDB,ChildTable,ChildKeyColumn,ParentDB,ParentTable,ParentKeyColumn from DBC.All_RI_Children

where parenttable='test1'

and ParentDB='tmp_test_20171120';

 

ALTER TABLE tmp_test_20171120.test2 DROP FOREIGN KEY ( a1 ) REFERENCES test1( a ); 

  *** Table has been modified.

 

 DROP TABLE test1;

  *** Table has been dropped.

 

Option 2: Drop Child table before Parent Table

  drop table test2;

  *** Table has been dropped.

 

 drop table test1;

  *** Table has been dropped.

 

Comments