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.
|