Today I was writing an sql script for cleaning out data from a lot of tables in an Oracle database, and wanted to figure out in which order to delete data from the tables. I found this SQL script on http://blog.mclaughlinsoftware.com/2009/03/05/validating-foreign-keys/ which helped a lot:
SELECT UC.OWNER
, UC.CONSTRAINT_NAME
, UCC1.TABLE_NAME||’.’||UCC1.COLUMN_NAME “CONSTRAINT_SOURCE”
, ‘REFERENCES’
, UCC2.TABLE_NAME||’.’||UCC2.COLUMN_NAME “REFERENCES_COLUMN”
FROM USER_CONSTRAINTS uc
, USER_CONS_COLUMNS ucc1
, USER_CONS_COLUMNS ucc2
WHERE UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
AND UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME
AND UCC1.POSITION = UCC2.POSITION — Correction for multiple column primary keys.
AND UC.CONSTRAINT_TYPE = ‘R’
ORDER BY UCC1.TABLE_NAME
, UC.CONSTRAINT_NAME;
Leave a comment