0

Is there a clean, simple, and preferably prepackaged (ready function?) way to list the order of referential integrity in a Postgres database, rather than trying to read the system tables to find that order manually, which is tedious and time consuming?

What I mean is, if the entity hierarchy is

USER --> ACCOUNT --> TRANSACTION

meaning that a single user can have multiple accounts and that a single account can have multiple transactions, if I want to delete all the three tables without breaking any foreign keys, I would have to do it in the exact following order:

DELETE FROM TRANSACTION;
DELETE FROM ACCOUNT;
DELETE FROM USER;

Now this is a very simple and obvious example. But real life databases have a lot more tables and foreign keys. If I wanted to delete every table down to 0 rows, is there a quick way to obtain the exact order of referential integrity to do it in?

  • 3
    Write a script to do data dictionary diving, given a table name? – Philᵀᴹ Oct 14 '16 at 15:08
  • i was hoping this function could be prepackaged – amphibient Oct 14 '16 at 15:14
  • also, i am not familiar with the term data dictionary diving – amphibient Oct 14 '16 at 15:15
  • There nay be cycles in the foreign keys graph (a table referencing itself or A -> B -> C -> A) – ypercubeᵀᴹ Oct 14 '16 at 16:18
  • i get that @ypercubeᵀᴹ. but wouldn't that be indicative of a less-than-perfect DB design (which is a separate issue from the one at hand) ? – amphibient Oct 14 '16 at 16:22
2

You can "dive" into the system tables and get all the tables that reference a specific table (and all those that reference them, etc). It will be a recursive query, not very difficult to write.

However, for the simple operation you want to to perform, there is a simple solution:
TRUNCATE with the magic option CASCADE:

truncate table a cascade ;

Tested at rextester.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.